Миллисекунды неправильно при конвертации из XML в SQL Server datetime

У меня возникла проблема, связанная с преобразованием datetime из XML (ISO8601: yyyy-mm-ddThh: mi: ss.mmm) в дату и время SQL Server 2005. Проблема в том, что преобразование миллисекунд неверно. Я тестировал неявное и явное преобразование, используя convert (datetime, MyDate, 126) из nvarchar, и результат тот же:

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. Является ли это простой проблемой округления? Вариация Millisecond важна, и потеря/выигрыш один или два не является вариантом.

Ответ 1

Да, SQL Server округляет время до 3.(3) миллисекунд:

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 в десятичной форме.

Это потому, что SQL Server хранит time часть DATETIME как число 1/300 вторых тиков с полуночи.

Если вам нужна более высокая точность, вам нужно сохранить часть time в качестве отдельного значения. Например, время хранения округляется до секунды, как DATETIME, и миллисекунды, или любую другую точность, которая вам нужна как INTEGER в других столбцах.

Это позволит вам использовать сложную арифметику DATETIME, например, добавить месяцы или найти дни недели на DATETIME, и вы можете просто добавить или вычесть миллисекунды и объединить результат как .XXXXXX+HH:MM, чтобы получить достоверность XML представление.

Ответ 2

Из-за проблем с упомянутых Quassnoi, если у вас есть возможность использовать SqlServer 2008, вы можете использовать datetime2 datatype или если вас беспокоит только временная часть, вы можете использовать тип данных времени

Типы данных даты и времени - перечислены все типы и их точность

В Sql Server 2005, если мне нужна точность 1 миллисекунды, я бы добавил дополнительный столбец milisecond типа int, чтобы сохранить количество миллисекунд и удалить часть miliseconds из столбца dateTime (установите его на 000). Это предполагает, что вам также нужна информация о дате.