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

DECLARE
    @start_date datetime
,   @current_date datetime

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

SELECT 
    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