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

Question

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 1

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