日期之间的 T-SQL 混淆

发布于 2024-10-26 04:47:24 字数 950 浏览 2 评论 0原文

我正在 SQL Server 2000 中使用 T-SQL，并且有一个表 TRANSACTIONS，其中有一个日期列 TRAANDATE 定义为 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

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

是因为 TRAANDATE 定义为“DATETIME”的方式吗？

基于这一发现，我认为必须检查所有这些旧代码，因为这些 BETWEEN 运算符遍布在这个旧系统中，而且看起来它没有正确提取所有数据。我只是想先从一些人那里得到澄清。谢谢！

原文

I am working with T-SQL in SQL Server 2000 and I have a table TRANSACTIONS which has a date column TRANDATE defined as DateTime, among many other columns which are irrelevant for this question..

The table is populated with transactions spanning many years. I ran into code, test, that has me confused. There is a simple SELECT, like this:

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

and its not returning two rows of data that I know are in that table.

With the statement above, the last row its returning, in order, has a TRANDATE of:
2010-12-31 00:00:00.000

When I modify the statement like below, I get the additional two rows for December 2010 that are in that table:

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

I have tried to find out why the BETWEEN operator doesnt include ALL rows for the 24 period in 12/31/2010 when using the first SELECT, above. And why does it need to have the explicit hours added to the SELECT statement as in the second, modified, statement to get it to pull the correct number of rows out?

Is it because of the way TRANDATE is defined as "DATETIME"?

Based on this finding, I think that am going to have to go through all of this old code because these BETWEEN operators are littered throughout this old system and it seems like its not pulling all of the data properly. I just wanted clarification from some folks first. Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

離殇 2024-11-02 04:47:24

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

'12/31/2010' 也是一个要点。也就是说，现在是 12 月 31 日午夜。
此后发生的所有事情都将被忽略。
这正是您想要的行为（即使您还没有意识到）。

不要认为当您选择省略时间部分时，它会神奇地假定为“any”。这将是“全零”，即午夜。

如果您想在查询中包含一整天，而不必指定 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 子句中，它是可控制的）。

A date is a point in time, not a time span.

'12/31/2010' is a point, too. Namely, it's the midnight of the 31st of December.
Everything that happened after this point is ignored.
That's exactly the behaviour you want (even if you haven't realised that yet).

Do not think that when you choose to omit the time part, it is magically assumed to be "any". It's going to be "all zeroes", that is, the midnight.

If you want to include the entire day in your query without having to specify 23:59:59 (which, by the way, excludes the last second of the day, between the moment 23:59:59 of the current day and the moment 00:00:00 of the next day), you can do that either by using strict inequalities (>, <) bounded by the first points of time you don't want:

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

or by comparing date values casted to DATE:

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

(it is okay to put this type of cast in a WHERE clause, it is sargable).

回复收藏 0 原文

书信已泛黄 2024-11-02 04:47:24

正如您所发现的，如果您在输入日期时未指定时间，则默认为该日期上午的午夜。因此，12/31/2010 在当天开始时的午夜停止。

要获取 2010 年 12 月 31 日的所有日期，您可以指定时间（如您所做的那样），或在结束日期后添加一天。如果没有时间，1/1/2011 将在 12/31/2010 午夜钟声敲响时结束。因此，您可以在2010年12月1日和2011年1月1日之间进行。您可以使用 DATEADD 在 SQL 中添加日期（如果这样更容易的话）。

第二种增加一天的方法存在一些风险。您将获得 2011 年 1 月 1 日时间为 00:00:00 的所有记录。

以下是执行 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)

然后在 的 WHERE 子句中使用 @ToDate BETWEEN 短语以通常的方式。

As you have discovered, if you don't specify a time when entering a date, it defaults to midnight in the morning of the date. So 12/31/2010 stops at midnight when that day begins.

To get all dates for 12/31/2010, you can either specify the time, as you have done, or add one day to the ending date. Without a time, 1/1/2011 ends at the stroke of midnight on 12/31/2010. So, you could do BETWEEN 12/1/2010 AND 1/1/2011. You can use DATEADD to add the day in your SQL if that makes it easier.

There is some risk in that second approach of adding a day. You will get any records for 1/1/2011 that carry the time of 00:00:00.

Here's one way to perform the 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)

Then you use @ToDate in your WHERE clause in the BETWEEN phrase in the usual way.

回复收藏 0 原文

不一样的天空 2024-11-02 04:47:24

“12/01/2010”表示“12/01/2010 00:00:00”，“12/31/2010”表示“12/31/2010 00:00:00”。这就是为什么 2010 年 12 月 31 日当天晚些时候的日期时间值被排除在查询结果之外。

回复收藏 0 原文

蒗幽 2024-11-02 04:47:24

如果我准确地执行此操作，您的预期结果是什么

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

： 12-31-2010 00:00:00

那么您为什么会期望它作为查询的参数有所不同？

What would be your expected result if I would do this

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

Exactly: 12-31-2010 00:00:00

So why would you expect it to be different as argument for a query?

回复收藏 0 原文

惯饮孤独 2024-11-02 04:47:24

您已经回答了自己的问题。您所观察到的是 SQL Server 的工作方式。

如果您需要确认，此MSDN文档有以下内容它

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

编辑

至于您的评论，日期时间本质上是一个浮点值。

以下脚本显示了 SQL Server 使用的数字。
当上限为 40541 (12/31/2010) 时，无法包含 40541.9749 (12/31/2010 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

You have kind of answered your own question already. What you have observed is the way SQL Server works.

If it is confirmation you need, this MSDN document has following to say about it

When the time part is unspecified, it
defaults to 12:00 A.M. Note that a row
that contains a time part that is
after 12:00 A.M. on 1998-0105 would
not be returned by this query because
it falls outside the range.

Edit

As for your comment, a datetime essentially is a floating point value.

Following script shows what numbers SQL Server works with.
40541.9749 (12/31/2010 23:23:59) can't be included when your upper bound is 40541 (12/31/2010)

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

回复收藏 0 原文

~没有更多了~