A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, July 8, 2010

Datediff, not doing what you think it does

I think two different defects logged in 7 days qualifies for a blog post.

datediff is not not the inverse of dateadd

Are you using datediff and have you noticed it's rounding on you? It's not rounding, it's doing exactly what it's supposed to. Really, read BOL and you'll see the issue is that datediff doesn't do what you might think it does. Of note, "Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate."

As a quick example, see below. Start date is June 2000 and it assumes it is January 1 2010. That is 9.5 years later. Adding 10 years (dateadd) to our start date will show the correct date of 2010-06-01. Datediff also correctly identifies that 10 year boundaries have been crossed between 2000 and 2010

    @start_date datetime
,   @current_date datetime

    @start_date = '2000-06-01'
,   @current_date = '2010-01-01'

    datediff(year, @start_date, @current_date) AS nine_and_half_years
,   dateadd(year, 10, @start_date) AS ten_years
nine_and_half_years     ten_years
10                      2010-06-01 00:00:00.000
So, in conclusion

datediff is not not the inverse of dateadd

References http://msdn.microsoft.com/en-us/library/ms187081(SQL.90).aspx Comparing DATEADD and DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794.aspx datediff
http://msdn.microsoft.com/en-us/library/ms186819.aspx dateadd


Trevor Wayne said...

You could write an entire blog about questions I ask you, couldn't you? Thanks for this info, mu brain was in knots trying to figure it out

Peter Schott said...

However, it does behave somewhat unexpectedly if you do a SET DATEFIRST to change the weekday. We've done that to make weekends fall together for scheduling purposes rather than have Saturday as the tail of one week and Sunday as the start of the next. DateDiff treats that as a different week even though we've specified that they should be the same week. We found a workaround, but it sure seems to act like a bug in those cases.

Still, I appreciate the reminder. Those are the sort of things that can be really frustrating until you figure out what's going on behind the scenes.