SQL查询日期范围使用Between

Question

我刚刚运行了这个查询。

Select * 
from ProjectData 
where logtime between '2012-09-25 12:00:00.000' and '2012-09-25 12:59:59.999' 
order by LogTime

为了在12小时内找到所有的记录，我们每秒都记录一次，因此我期望会有3600条记录，但令我惊讶的是，我得到了3601条记录，最后一条记录的时间是

2012-09-25 13:00:00.000

有没有想法为什么会选择这条记录？即使Between包括给定的值，但这个值仍然高于条件。我正在使用SQL Server 2012 Express版。

- Sumit Gupta

---

你的数据表中的日期时间字段的精度可能不允许进行如此细微的区分，比如它能否将2012-09-25 12:59:59.999和2012-09-25 13:00:00.000这样的日期区分开来。也许你应该将搜索限制在2012-09-25 12:59:59作为上限条件？ - Carsten Massmann

我有一个 DateTime 数据类型，当我查询表时，它会显示毫秒级的时间。所以我猜它是以这种方式存储的？ - Sumit Gupta

DATETIME的精度为3.33毫秒-因此在您的情况下，您的第二个值将会被“四舍五入”到2012-09-25 13:00:00.000，因为DATETIME可以容纳以.990、.993、.997和.000结尾的值。如果您坚持使用DATETIME，可以改用DATETIME2（精度更高！），或者使用'2012-09-25 12:59:59.997'作为您的第二个值。 - marc_s

@marc_s 感谢提供信息，但是相对于datetime类型，使用Datetime2会对性能造成影响吗？因为我们的数据库中有数百万条记录。 - Sumit Gupta

使用 DATETIME2，您可以根据需要选择精度；带有三位小数秒（精度：1毫秒）的 DATETIME2(3) 实际上比 DATETIME 使用更少的存储空间（7个字节而不是8个），因此不会出现负性能影响。 - marc_s

Answer 1

尝试使用DATETIME2数据类型来存储logtime列的时间戳- 查询：

DECLARE @temp TABLE (logtime DATETIME2)
INSERT INTO @temp (logtime)
VALUES 
    ('20120925 12:00:00.000'),
    ('20120925 12:59:59.999'),
    ('20120925 13:00:00.000')

SELECT *
FROM @temp
WHERE logtime BETWEEN '2012-09-25 12:00:00.000' AND '2012-09-25 12:59:59.999'
ORDER BY logtime

输出：

logtime
---------------------------
2012-09-25 12:00:00.0000000
2012-09-25 12:59:59.9990000

DATETIME与DATETIME2的区别：

SELECT name, [precision]
FROM sys.types
WHERE name IN ('datetime', 'datetime2')

输出：

name        precision
----------- ---------
datetime2   27
datetime    23

- Devart

---

根据您的建议，我运行了Select * from Project_5_Data where Cast(logtime as DateTime2) between '2012-09-25 12:00:00.000' and '2012-09-25 12:59:59.999' order by LogTime，并且它显示了正确的结果。因此，另一个问题出现了，如果DateTime不支持毫秒，为什么输出中会显示毫秒？ - Sumit Gupta

@Devart：很好的回答。你能解释一下DateTime的这种行为吗？ - Ravi Singh

@Ravi Singh，每种数据类型都有精度限制。DATETIME具有较低的精度，因此服务器被迫使用四舍五入毫秒。 - Devart

Answer 2

You have used Datetime as a data type, which can be rounded to increments of .000, .003, or .007 seconds. For more information, please refer to this link.

For example, please see SQL Fiddle.

Below is the schema setup for MS SQL Server 2012 and query 1:

Declare @testtime datetime = '2012-09-25 12:59:59.999'

select @testtime

Results:

|                         COLUMN_0 |
------------------------------------
| September, 25 2012 13:00:00+0000 |

- Ravi Singh

Answer 3

尝试执行此查询。

SELECT CAST('2012-09-25 12:59:59.999' AS DATETIME)

这将输出2012-09-25 13:00:00.000。所以我认为这就是你的结果包含时间2012-09-25 13:00:00.000的原因。

实际上，它选择了2012-09-25 12:00:00.000和2012-09-25 13:00:00.000之间的值。

- Nithesh Narayanan

---

它确实支持，但我的问题是为什么...我的服务器上的DateTime确实显示毫秒，因此它支持该格式，因此不应将其四舍五入。 - Sumit Gupta

实际上它支持毫秒，但在从字符串转换为日期时间时会丢失精度，并以0.000、0.003、0.007的增量四舍五入值。请参阅此链接以获取更多详细信息：http://msdn.microsoft.com/en-us/library/ms187819.aspx - Nitin Agrawal