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');
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.
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
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
8 as well.