T-SQL日期范围查询混淆

Question

我正在使用SQL Server 2000中的T-SQL，并且我有一个表TRANSACTIONS，其中有一个日期列TRANDATE，定义为DateTime，还有其他许多与此问题无关的列。

该表填充了跨越多年的交易。 我遇到了一些困惑我的代码和测试。 有一个简单的SELECT查询，像这样：

SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010' and '12/31/2010' ORDER BY TRANDATE

它没有返回我知道在该表中的两行数据。

使用上述语句，按顺序返回的最后一行具有TRANDATE为： 2010-12-31 00:00:00.000

当我修改语句如下所示时，我会得到该表中存在的2010年12月份的另外两行：

SELECT TRANDATE, RECEIPTNUMBER FROM TRANSACTIONS WHERE TRANDATE BETWEEN '12/01/2010 00:00:00' and '12/31/2010 23:59:59' ORDER BY TRANDATE

我试图找出为什么在使用上面的第一个SELECT语句时，BETWEEN运算符不包括2010年12月31日24小时内的所有行。为什么需要将显式小时添加到第二个修改后的语句中的SELECT语句中才能使其正确地提取出正确数量的行？

这是因为TRANDATE被定义为"DATETIME"的方式吗？

基于这一发现，我认为我将不得不检查所有旧代码，因为这些BETWEEN运算符散布在整个旧系统中，而且似乎没有正确地提取所有数据。我只是想先向一些人澄清一下。谢谢！

- ONDEV

---

"12/31/2010 23:59:59" 仍然会错过 "12/31/2010 23:59:59" 到 "12/31/2010 23:59:59:997" 之间的时间。在日期时间上不要使用 between，而是使用 >= 和 < 来代替。 - Martin Smith

@Martin：谢谢。还要感谢其他所有人，特别是@GSerg，他基本上回答了我下面的问题。似乎我需要15个积分才能点赞，所以你们看不到我的点赞。但现在我明白了大家的回复。谢谢。我有一些工作要做！ :-) - ONDEV

Answer 1

日期是一个时间点，而不是一个时间跨度。

'12/31/2010' 也是一个时间点。具体来说，它是12月31日午夜的时间点。
这个时间点之后发生的一切都会被忽略掉。
这正是你想要的行为（即使你还没有意识到这一点）。

不要认为当你选择省略时间部分时，它会神奇地被认为是 "任意时间"。实际上，它将被认为是"全零"，也就是午夜。

如果你想在查询中包含整个日期，而又不必指定 23:59:59 （顺便说一下，这种方式排除了这一天的最后一秒，即当前日期的23:59:59时刻和第二天的00:00:00时刻之间的那一瞬间），你可以使用严格的不等式（>、<），并以你不希望的时间点为边界：

WHERE TRANDATE >='12/01/2010 00:00:00' and TRANDATE < '01/01/2011'

或者通过将日期值转换为 DATE 进行比较：

WHERE CAST(TRANDATE AS DATE) between '12/01/2010' and '12/31/2010'

（在WHERE子句中使用这种类型的转换是可以的，它是可搜索谓词。）

- GSerg

Answer 2

如果您在输入日期时没有指定时间，则默认为该日期的午夜。因此，2010年12月31日将在当天开始的午夜停止。

要获得2010年12月31日的所有日期，您可以指定时间（就像您已经做的那样），或者在结束日期上加一天。如果没有指定时间，1/1/2011将在2010年12月31日午夜的时刻结束。所以，您可以使用BETWEEN 12/1/2010 AND 1/1/2011。如果需要，您可以在SQL中使用DATEADD来添加一天。

第二种方法中添加一天存在一些风险。您将获取任何时间为00:00:00的记录，包括2011年1月1日的记录。

以下是使用DATEADD的一种方法：

DECLARE @FromDate datetime, @ToDate datetime
// These might be stored procedure input parameters
SET @FromDate = '12/1/2010'
SET @ToDate = '12/31/2010'

SET @ToDate = DATEADD(d, 1, @ToDate)

然后您可以像往常一样在BETWEEN短语中的WHERE子句中使用@ToDate。

- DOK

Answer 3

'

'12/01/2010'代表的是'12/01/2010 00:00:00'，而'12/31/2010'代表的是'12/31/2010 00:00:00'。这就是为什么那些日期在12/31/2010这一天后的时间值被排除在你的查询结果之外。

'

- Greg

Answer 4

如果我这样做的话，你会期望得到什么结果？

Insert "12/31/2010" into your datetime column?

确切地说：2010年12月31日 00:00:00

那么，您为什么指望将其作为查询参数时会有所不同呢？

- Pleun

Answer 5

你已经在某种程度上回答了自己的问题。你所观察到的就是 SQL Server 的工作方式。

如果你需要确认，这个 MSDN 文档 对此有以下说明：

当时间部分未指定时，默认为上午 12:00。请注意，包含日期为 1998-0105 上午 12:00 之后的时间部分的行不会被此查询返回，因为它超出了范围。

编辑

至于你的评论，datetime 本质上是一个浮点值。

以下脚本显示 SQL Server 使用的数字。
当你的上限是 40541（2010 年 12 月 31 日）时，40541.9749（2010 年 12 月 31 日 23:23:59）不能被包括在内。

DECLARE @ADateTime1 DATETIME
DECLARE @ADateTime2 DATETIME
DECLARE @ADateTime1AsFloat FLOAT
DECLARE @ADateTime2AsFloat FLOAT

SET @ADateTime1 = '12/31/2010'
SET @ADateTime2 = '12/31/2010 23:23:59'

SET @ADateTime1AsFloat = CAST(@ADateTime1 AS FLOAT)
SET @ADateTime2AsFloat = CAST(@ADateTime2 AS FLOAT)

SELECT @ADateTime1AsFloat, @ADateTime2AsFloat

- Lieven Keersmaekers

---

好的，但这是我困惑的地方。你说得对，我似乎“理解”它在做什么，但为什么会让我感到困惑的是为什么不带小时的BETWEEN语句无法捕捉到2010-12-31 18:12:54.000这一行数据？无论时间是00:00:00、18:12:54还是23:59:59，12/31/2010仍然是12/31/2010！ - ONDEV