4

I noticed that 2 datetimes are considered equal if one is 

2010-12-31 15:13:48.000

and the other 

2010-12-31 15:13:48.001.

I thought in the beginning that it was rounding up or down to nearest full seconds but it turned out 

2010-12-31 15:13:48.002

registers the difference correctly.

Any idea why this happens?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
El General Magnifico
  • 107
  • 1
  • 7

2 Answers2

7

The datetime data type is accurate to 3 milliseconds (0.0033 seconds), so your results make sense. Values are rounded to 0.000, 0.003, and 0.007. So, your first example is rounded to: "2010-12-31 15:13:48.000" and the second to "2010-12-31 15:13:48.003".

You can read about this in the documentation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes totally right. You can see this too here https://msdn.microsoft.com/en-us/library/ms187819.aspx – Ionic Jun 19 '15 at 10:51
  • 1
    I would add that datetime2 is extreamly more accurate (100 nanoseconds), so if milliseconds are needed, datetime2 is the datatype to choose. – Zohar Peled Jun 19 '15 at 11:05
0

SQL Server stores datetimes to the precision of 1/300 of a second (~3.33 milliseconds), as stated by the documentation:

ate and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

So depending on where exactly the rounding occurs, timestamps that differ by up to three milliseconds may be considered equal.

Mureinik
  • 297,002
  • 52
  • 306
  • 350