How do I select the number of distinct days in a date range?
- by isme
I'm trying to use the T-SQL function DATEDIFF to select the number of distinct dates in a time period.
The following query:
SELECT DATEDIFF(DAY, '2012-01-01 01:23:45', '2012-01-02 01:23:45')
selects 1, which is one less than I want. There are two distinct dates in the range: 2012-01-01 and 2012-01-02.
It is not correct to add one to the result in the general case. The following query:
SELECT DATEDIFF(DAY, '2012-01-01 00:00:00', '2012-01-02 00:00:00')
selects 1, which is correct, because there is only one distinct date in the range.
I'm sure there is a simple bit of arithmetic that I'm missing to calculate this. Can someone help me?
