3

The following query:

select convert(datetime, '2016-06-20 7:22:52.728')

gives me:

2016-06-20 07:22:52.727

In SQL Server v12.0.4100.1.

Whatever value I put for milliseconds, the result always has 1 less millisecond.

Am I missing something or is this a bug?

Mike W
  • 425
  • 3
  • 12
  • 1
    See http://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond – John Cappelletti Mar 23 '17 at 17:04
  • I'd like to state that the Search tool on here is not very good because I did search for an existing answer before posting. Thanks for the answers everyone! – Mike W Mar 23 '17 at 17:26

2 Answers2

6

It's not a bug. The resolution of datetime is such that the final digit can only be one of a few values... Rounded to increments of .000, .003, or .007 seconds.

This is documented: Microsoft documentation for datetime (Transact-SQL)

pmbAustin
  • 3,890
  • 1
  • 22
  • 33
3

That is how datetime works in sql server. If you want that millisecond, switch to datetime2([3-7]).

datetime accuracy is to 0.00333 second.

datetime2 accuracy is to 100 nanoseconds.

Date and Time Data Types and Functions (Transact-SQL)

Similarly, if you want to get the server time with additional accuracy, you would use sysdatetime() which returns datetime2(7) instead of getdate() which returns datetime.

SqlZim
  • 37,248
  • 6
  • 41
  • 59