T-SQL日期之间的混乱 [英] T-SQL Between Dates Confusion

问题描述

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

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

该表填充了多年的交易记录.我遇到了代码，测试，这让我感到困惑.有一个简单的SELECT，如下所示:

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.

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

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

当我修改如下语句时，将获得该表中2010年12月的另外两行:

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

我试图找出为什么BETWEEN运算符在使用上面的第一个SELECT时没有包含12/31/2010中24个周期的所有行.又为什么需要像在第二条修改后的语句中那样，将显式的小时数添加到SELECT语句中，才能获取正确的行数呢?

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?

是因为TRANDATE被定义为"DATETIME"的原因吗?

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

基于此发现，我认为这将必须遍历所有旧代码，因为这些BETWEEN运算符在整个旧系统中无处不在，并且似乎无法正确提取所有数据.我只是想先从一些人那里澄清一下.谢谢！

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!

推荐答案

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

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

'12/31/2010'也很重要.即是12月31日的午夜.
此后发生的一切都将被忽略.
这正是您想要的行为(即使您尚未意识到这一点).

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

不要以为当您选择省略时间部分时，就神奇地假定它为"any".将会是"all zeroes"，即午夜.

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.

如果要在查询中包括一整天而不必指定23:59:59(顺便说一句，

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'

或通过比较强制转换为DATE的日期值:

or by comparing date values casted to DATE:

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

(可以在WHERE子句中放入这种类型的转换，它是可精的)

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

这篇关于T-SQL日期之间的混乱的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！