Milliseconds wrong when converting from XML to SQL Server datetime

Question

I have a problem with converting datetime from XML (ISO8601: yyyy-mm-ddThh: mi: ss.mmm) to SQL Server 2005 date and time. The problem is that the millisecond conversion is not correct. I tested the 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-invasive testing shows that the last digit is 0, 3, or 7. Is this a simple rounding problem? Millisecond variation is important, and losing one or two is not an option.

Answer 1

Yes, SQL Server rounds the 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 differ by 1 second, and their binary representations differ by 0x12C , i.e. 300 in decimal.

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

If you need higher precision, you need to keep the time part as a separate value. For example, the storage time is rounded to the second, like DATETIME , and milliseconds, or any other precision that you need as INTEGER in other columns.

This will allow you to use complex DATETIME arithmetic, for example, add months or find the days of the week on DATETIME , and you can simply add or subtract milliseconds and combine the result as .XXXXXX+HH:MM to get the validity of the XML representation.

Answer 2

Due to issues with the mentioned Quassnoi , if you have the option to use SqlServer 2008, you can use datetime2 datatype or if you are only concerned with the time part, you can use the time data type

Date and time data types - lists all types and their accuracy

In Sql Server 2005, if I need 1 millisecond precision, I would add an extra int milisecond column to save the number of milliseconds and remove the miliseconds part from the dateTime column (set it to 000). This suggests that you also need date information.