Milliseconds wrong when converting from XML to SQL Server datetime

by

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.

More Related Contents:

  1. How to query for Xml values and attributes from table in SQL Server?
  2. Parse XML in SQL Server
  3. Check if table exists in SQL Server
  4. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  5. Simulation of CONNECT BY PRIOR of Oracle in SQL Server
  6. Datatype for storing ip address in SQL Server
  7. best way to convert and validate a date string
  8. Why use the INCLUDE clause when creating an index?
  9. Creating audit triggers in SQL Server
  10. Regular Expressions in SQL Server servers?
  11. How do you specify a different port number in SQL Management Studio?
  12. Diagnosing Deadlocks in SQL Server 2005
  13. Disadvantages of MARS (Multiple Active Result Sets)?
  14. Aggregate bitwise-OR in a subquery
  15. Check if a temporary table exists and delete if it exists before creating a temporary table
  16. Drop primary key using script in SQL Server database [duplicate]
  17. sql use statement with variable
  18. Decimal values in SQL for dividing results
  19. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  20. SQL Server Xml query with multiple namespaces
  21. How to store directory / hierarchy / tree structure in the database?
  22. INFORMATION_SCHEMA vs sysobjects
  23. Bulk insert fixed width fields
  24. Does query plan optimizer works well with joined/filtered table-valued functions?
  25. Get row where datetime column = today
  26. Table Valued Function Killing My Query Performance
  27. Full text catalog/index search for %book%
  28. @@ERROR and/or TRY – CATCH
  29. Does the `nodes()` method keep the document order?
  30. Why insert-select to variable table from XML variable so slow?

Leave a Comment