How do I select the number of distinct days in a date range?

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?

Answers


SELECT DATEDIFF(DAY, '2012-01-01 01:23:45', '2012-01-02 01:23:45') 

Given this example, it should still be 1, because there is only one day that has passed. Even if you are considering the start of a day, it would still be only one (as this range includes the start of only 2012-01-02 00:00:00).

Your logic for:

SELECT DATEDIFF(DAY, '2012-01-01 01:23:45', '2012-01-02 01:23:45') 

and

SELECT DATEDIFF(DAY, '2012-01-01 00:00:00', '2012-01-02 00:00:00') 

Should be the same, as mathematically they are the same range. DATEDIFF compares based on the granularity of the first paramter. You are comparing by day, so SQL Server will see 2012-01-01 to 2012-01-02 as a 1 day difference.

An extremely ugly (and in my opinion, bad) workaround would be something like this:

SELECT DATEDIFF(day, yourStartDate, dateadd(ss, -1, yourEndDate)) + 1

What this would do is handle inclusive dates. So you could basically have this:

SELECT DATEDIFF(DAY, '2012-01-01 01:23:45', dateadd(ss, -1, '2012-01-02 01:23:45')) + 1 

Would equal 2 and this:

SELECT DATEDIFF(DAY, '2012-01-01 00:00:00', dateadd(ss, -1, '2012-01-02 00:00:00')) + 1

Would equal 1. I don't think this is the best idea in the world, but it will give you your desired output. It all boils down to business logic.


Need Your Help

Multiple PHP buttons? (One button is a submit button the other buttons are functions)

php javascript button

I'm making a php site that has multiple buttons. One button saves, which requires it to be taken to a different page but the other buttons do simple things like add +1 to a score.

Cygwin console jabber client

cygwin xmpp

Do there exist any cygwin console jabber clients? I've not successfully compiled any of the console clients listed here in cygwin.