以毫秒为单位的datetime字段查询在SQL Server中给出错误的结果 [英] Query on datetime fields with milliseconds gives wrong result in SQL Server

问题描述

我在SQL Server 2005中使用datetime字段遇到了一个奇怪的错误。datetime字段以毫秒级的准确性显示，但是看起来毫秒不总是被使用。这是我的测试查询：

  SELECT col1，YEAR（col1）AS yr，MONTH（col1）AS mn，DAY（col1） AS dy 
 FROM mytable 
 WHERE col1> ='2009-12-31 00：00：00.0'AND col1< ='2009-12-31 23：59：59.999'
 ORDER BY col1 
  

在我的结果中，我得到：

 
 col1 |年| mn | dy 
 ---------------------------- + ------ + ---- + ---- 
 2009-12-31 00：00：00：00.000 | 2009 | 12 | 31 
 2010-01-01 00：00：00：00.000 | 2010 | 1 | 1 
 

问题是我得到了2010-01-01的日期，即使不应该小于或等于 2009-12-31 23：59：59.999。但是如果将查询更改为使用2009-12-31 23：59：59.99 8 ，则可以正常工作（不返回2010年数据时间）。

$ b $这是一个bug，还是SQL Server的工作原理？如果这是它的工作原理，有没有理由呢？我遇到这种迁移MySQL的一些查询，这样可以按预期方式工作（即使MySQL甚至不存储毫秒！）。

解决方案

SQL Server 将时间部分作为 1/300 的数量，从午夜开始第二个长时间。 p>

23：59：59.999 四舍五入到最接近的滴答，恰好是 00:00 ：00.000 第二天。

  SELECT CAST（CAST（'2009-12-01 00 ：00：00.000'AS DATETIME）作为BINARY（8）），
 CAST（CAST（'2009-12-01 23：59：59.997'AS DATETIME）as BINARY（8）），
 CAST （CAST（'2009-12-01 23：59：59.999'AS DATETIME）AS BINARY（8））
 
 
 
 0x00009B8F 00000000 0x00009B8F 018B81FF 0x00009B90 00000000 
  

在第一个值中，日期部分 0x9B8F （ 39823 ）是自 1900年1月1日起的天数，而时间部分 0 ，是t他自午夜以来的滴答数。

在第二个值中， 0x018B81FF （ 25919999 或 24 * 60 * 60 * 300 - 1 ）是自午夜以来最多可能的滴答数。

最后，第三个值在时间部分中的 0 ，日期部分增加了一个。

I'm running into an odd bug using datetime fields in SQL Server 2005. The datetime field shows up with millisecond-level accuracy, but it looks like the milliseconds are not always used. Here's my test query:

SELECT col1, YEAR(col1) AS yr, MONTH(col1) AS mn, DAY(col1) AS dy
FROM mytable
WHERE col1 >= '2009-12-31 00:00:00.0' AND col1 <= '2009-12-31 23:59:59.999'
ORDER BY col1

In my results I get:

 col1                       |  yr  | mn | dy
----------------------------+------+----+----
 2009-12-31 00:00:00:00.000 | 2009 | 12 | 31
 2010-01-01 00:00:00:00.000 | 2010 |  1 |  1

The problem is that I got the 2010-01-01 date, even though that shouldn't be less than or equal to "2009-12-31 23:59:59.999". But if I change the query to use "2009-12-31 23:59:59.998" it works OK (no 2010 datetimes are returned).

Is this a bug, or is this just how SQL Server works? If this is how it works, is there some reason for it? I ran into this migrating some queries from MySQL, where this works as expected (even though MySQL doesn't even store the milliseconds!).

解决方案

SQL Server stores time part as number of 1/300 second long ticks from the midnight.

23:59:59.999 gets rounded to the nearest tick which happens to be 00:00:00.000 of the next day.

SELECT  CAST(CAST('2009-12-01 00:00:00.000' AS DATETIME) AS BINARY(8)),
        CAST(CAST('2009-12-01 23:59:59.997' AS DATETIME) AS BINARY(8)),
        CAST(CAST('2009-12-01 23:59:59.999' AS DATETIME) AS BINARY(8))



0x00009B8F 00000000    0x00009B8F 018B81FF    0x00009B90 00000000

In the first value, the date part, 0x9B8F (39823) is the number of days since Jan 1st, 1900, and the time part, 0, is the number of ticks since midnight.

In the second value, 0x018B81FF (25919999, or 24 * 60 * 60 * 300 - 1) is the maximal possible number of ticks since midnight.

Finally, the third value has the 0 in the time part and the date part increased by one.

这篇关于以毫秒为单位的datetime字段查询在SQL Server中给出错误的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！