Ben, blogging: DateDiff function in SQL Server

This DateDiff function in SQL Server might not be working as you expected.

For example, now we are looking for records that are older than 4 hours, you would think to do it like that:

select * from table where DateDiff(hour, CreateDate, getdate()) >=4

but that is not correct. Assume the CreateDate is 05:59 and the current time is
09:00 , you will find that the this record is selected, as if this 3 hours and 1 minute record is "older than 4 hours."

We can easily confirm that using these 2 queries:

select datediff(hour, '2013-03-19 05:59', '2013-03-19 09:00')

The time difference is 3 hours and 1 minute, so I would expect this query to return “3”

select datediff(hour, '2013-03-19 05:00', '2013-03-19 09:59')

The time difference is 4 hours and 59 minute, so I would expect this query to return “4”

Actually, these two queries both return “4”.

So this DateDiff function is simply using the "hour" part of the 2 datetime to do the calculation. It use the "9" of the second datetime to substract the "5" of the first datetime to achieve "4".

Back to our initial question: How to look for records that are older than 4 hours? Use DateAdd:

Select * from table where DateAdd(hour, 4, CreateDate) >= getDate()

This will give a precise calculation of 4 hours.

Add on Sept 16, 2013:
The last query can do the work, but it wasted index, if any. The DB has to visit every item to calculate the DateAdd(hour, 4, CreateDate) in order to find out the candidates.
It's important not to calculate each column, if you want to DB to use existing index of this column.
So the query can be changed to: