John Nash John Nash - 1 month ago 5
SQL Question

Why does datediff return 1 day less than the actual difference?

Consider the following snippet of SQL:

Select DATEDIFF(dd, '2014-09-22 09:14:01.850','2014-09-24 17:14:53.243') -- 1


This returns "2", but I actually want "3", since I am calculating the total number of days attended by an employee. I.e. in the above case he has attended 3 days, but
datediff
shows 2. Is there any way to get around this?

Answer

DATEDIFF with the dd parameter returns the number of date boundaries between 2 dates.

So DATEDIFF(dd, '2014-09-22 09:14:01.850','2014-09-24 17:14:53.243') will return 2 - because it is counting 23rd, and 24th. If you want to count 22nd, 23rd, and 24th, then just add 1, e.g.

Select DATEDIFF(dd, '2014-09-22 09:14:01.850','2014-09-24 17:14:53.243') + 1
Comments