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: 322

Filed under:

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

Related posts about tsql