从 XML 转换为 SQL Server 日期时间时出现毫秒错误 [英] Milliseconds wrong when converting from XML to SQL Server datetime

问题描述

我遇到了与将日期时间从 XML (ISO8601: yyyy-mm-ddThh:mi:ss.mmm) 转换为 SQL Server 2005 日期时间相关的问题.问题是当转换毫秒是错误的.我已经使用 nvarchar 的 convert(datetime, MyDate, 126) 测试了隐式和显式转换，结果是一样的:

I've run into a problem related to converting datetimes from XML (ISO8601: yyyy-mm-ddThh:mi:ss.mmm) to SQL Server 2005 datetime. The problem is when converting the milliseconds are wrong. I've tested both implicit and explicit conversion using convert(datetime, MyDate, 126) from nvarchar, and the result is the same:

Original                Result
2009-10-29T15:43:12.990 2009-10-29 15:43:12.990
2009-10-29T15:43:12.991 2009-10-29 15:43:12.990
2009-10-29T15:43:12.992 2009-10-29 15:43:12.993
2009-10-29T15:43:12.993 2009-10-29 15:43:12.993
2009-10-29T15:43:12.994 2009-10-29 15:43:12.993
2009-10-29T15:43:12.995 2009-10-29 15:43:12.997
2009-10-29T15:43:12.996 2009-10-29 15:43:12.997
2009-10-29T15:43:12.997 2009-10-29 15:43:12.997
2009-10-29T15:43:12.998 2009-10-29 15:43:12.997
2009-10-29T15:43:12.999 2009-10-29 15:43:13.000

我的非广泛测试表明最后一位数字是 0、3 或 7.这是一个简单的舍入问题吗?毫秒精度很重要，失去/获得一两个不是一种选择.

My non-extensive testing shows that the last digit is either 0, 3 or 7. Is this a simple rounding problem? Millisecond precision is important, and losing/gaining one or two is not an option.

推荐答案

是的，SQL Server 将时间四舍五入到 3.(3) 毫秒:

Yes, SQL Server rounds time to 3.(3) milliseconds:

SELECT CAST(CAST('2009-01-01 00:00:00.000' AS DATETIME) AS BINARY(8))
SELECT CAST(CAST('2009-01-01 00:00:01.000' AS DATETIME) AS BINARY(8))

0x00009B8400000000
0x00009B840000012C

如您所见，这些DATETIME 相差1 秒，它们的二进制表示相差0x12C，即300 十进制.

As you can see, these DATETIME's differ by 1 second, and their binary representations differ by 0x12C, that is 300 in decimal.

这是因为 SQL Server 将 DATETIME 的 time 部分存储为 1/300从午夜开始的第二个滴答声.

This is because SQL Server stores the time part of the DATETIME as a number of 1/300 second ticks from the midnight.

如果您想要更高的精度，则需要将 TIME 部分存储为单独的值.例如，将四舍五入到秒的时间存储为 DATETIME，并将毫秒或您需要的任何精度作为 INTEGER 存储在另一列中.

If you want more precision, you need to store a TIME part as a separate value. Like, store time rounded to a second as a DATETIME, and milliseconds or whatever precision you need as an INTEGER in another columns.

这将让您使用复杂的 DATETIME 算术，例如在 DATETIME 上添加月份或查找工作日，您只需添加或减去毫秒并连接结果为 .XXXXXX+HH:MM 以获得有效的 XML 表示.

This will let you use complex DATETIME arithmetics, like adding months or finding week days on DATETIME's, and you can just add or substract the milliseconds and concatenate the result as .XXXXXX+HH:MM to get valid XML representation.

这篇关于从 XML 转换为 SQL Server 日期时间时出现毫秒错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！