This was an interesting thing I saw in a Question of the Day submission. I hadn’t thought about the issue, but apparently DATEADD truncates values rather than rounding them. I’m not sure why that is the case, but it is.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

Imagine that I have someone enter a value for the number of hours to include in a report. I enter 5 and the report divides this in half to go back 2.5 hours and forward 2.5 hours. I run this code at the top of my code block:

DECLARE @hours NUMERIC(4, 2) = 5;
DECLARE @start DATETIME, @end datetime
SET @start = DATEADD (hour, -@hours / 2, GETDATE ())
SET @end = DATEADD (hour, @hours / 2, GETDATE ())

Now, what do you think are the resulting start and end times? I’d assume this works and the function sorts out how much of an hour is .5 or .4 or whatever.

Here’s the interesting result. Look at the time interval in the end result.

It’s 4. I entered 5 hours, but I get 4 hours. I bet a lot of us would let this bug slip through as reading the datetimes we’d miss this wasn’t actually 5 hours.

Apparently DATEADD actually truncates a non-integer value. The parameter notes that the 2nd parameter, the number to add to the date value, resolves to an integer. It also notes that DATEAD truncates, not rounds, values that have a decimal fraction.

Those are two very important distinctions. That could result in calculations that are way off from what people expect if you are trying to include data in a query and you are trying to do parts of time. You might need to separately calculate all your different date/time parts.

If you need to do fractional work with dates, you can’t use DATEADD.

To me that seems lacy, but is it? Let me know.

SQL New Blogger

This is a short example of something that a person pointed out to me, and I never knew. I decided to make a quick test (the code above) and then write about this. I could have included other examples, or shown how this might mess up different situations in my code.

You could do the same thing in 30 minutes or less and point out an interesting piece of knowledge that your future employers might find interesting. They might even want to interview someone that learns things like this.