T-SQL Count the days between two days (the delay is not enough)

advertisements

We have a requirement to bill our customers per day. We bill for an asset's existence in our system on that day. So, I started with datediff...

select datediff(dd  ,'2015-04-24 12:59:32.050'  ,'2015-05-01 00:59:59.000');

Returns this:

7

But I need to count the following dates: 4/24,4/25,4/26,4/27,4/28,4/29, 4/30, 5/1, which are 8 days. So datediff isn't quite working right. I tried these variations below

--too simple, returns 7, i need it to return 8
select datediff(dd  ,'2015-04-24 12:59:32.050', '2015-05-01 23:59:59.000');

--looking better, this returns the 8 i need
select ceiling(datediff(hh,'2015-04-24 12:59:32.050', '2015-05-01 23:59:59.000')/24.0);

-- returns 7, even though the answer still needs to be 8. (changed enddate)
select ceiling(datediff(hh,'2015-04-24 12:59:32.050', '2015-05-01 00:59:59.000')/24.0);

So, my question... How, in SQL, would I derive the date count like i described, since I believe datediff counts the number of day boundaries crossed.... My current best approach is loop through each day in a cursor and count. Ick.


Use CONVERT to get rid of the time part, add 1 to get the desired result:

SELECT DATEDIFF(dd,
                CONVERT(DATE, '2015-04-24 12:59:32.050'),
                CONVERT(DATE, '2015-05-01 00:59:59.000')) + 1;

It turns out the time part does not play any significant role in DATEDIFF when dd is used as the datepart argument. Hence, CONVERT is redundant. This:

SELECT DATEDIFF(dd, '2015-04-24 23:59:59.59','2015-05-01 00:00:00.000') + 1

will return 8 as well.