Milliseconds wrong when converting from XML to SQL Server datetime

Question

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

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.

Answer 1

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

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

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

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.

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.

Answer 2

Because of the precision issues mentioned by Quassnoi if you have the option to use use SqlServer 2008 you can consider using datetime2 datatype or if you are only concerned about the time part you can use time datatype

Date and Time Data Types - lists all the types and theirs accuracy

In Sql Server 2005 if I needed precision of 1 millisecond I would add an extra column milisecond of type int to store the number of miliseconds and remove the miliseconds part from the dateTime column (set it to 000). That assuming that you need the date information as well.