How do I select the number of distinct days in a date range?
Posted
by
isme
on Stack Overflow
See other posts from Stack Overflow
or by isme
Published on 2011-11-29T17:34:44Z
Indexed on
2011/11/29
17:50 UTC
Read the original article
Hit count: 316
tsql
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?
© Stack Overflow or respective owner
