如何选择日期范围内的不同天数? [英] How do I select the number of distinct days in a date range?

问题描述

我正在尝试使用 T-SQL 函数 DATEDIFF 来选择一个时间段内不同日期的数量.

I'm trying to use the T-SQL function DATEDIFF to select the number of distinct dates in a time period.

以下查询:

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

选择1，比我想要的少1.该范围内有两个不同的日期:2012-01-01 和 2012-01-02.

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')

选择1，这是正确的，因为范围内只有一个不同的日期.

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?

推荐答案

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

给出这个例子，应该还是1，因为只有一天过去了.即使您正在考虑一天的开始，它仍然只有一个(因为此范围仅包括 2012-01-02 00:00:00 的开始)).

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).

您的逻辑:

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

和

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

应该相同，因为在数学上它们是相同的范围.DATEDIFF 根据第一个参数的粒度进行比较.您正在按 day 进行比较，因此 SQL Server 会将 2012-01-01 到 2012-01-02 视为 1 天差.

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 

等于 2 和这个:

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

等于 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.

这篇关于如何选择日期范围内的不同天数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！