9

Try it for yourself, maybe it is somehow related to my environment:

The query:

SELECT CAST('20140904 23:59:59.999' AS datetime)

The result:

2014-09-05 00:00:00.000

Please explain this phenomenon.

UPDATE: Alex mentioned that datetime values are rounded to increments of .000, .003, or .007 seconds. The question is why?

anar khalilov
  • 16,993
  • 9
  • 47
  • 62
  • 2
    There is already an answer for this ... take a look here ..... [here is an explanaition ...][1] [1]: http://stackoverflow.com/questions/1506446/t-sql-query-returning-items-it-shouldnt-be – kamokaze Sep 10 '14 at 07:59

2 Answers2

11

datetime values are rounded to increments of .000, .003, or .007 seconds

I think you should use 

SELECT CAST('20140904 23:59:59.997' AS datetime)

This returns:

 2014-09-04 23:59:59.997

You can find more information here: http://msdn.microsoft.com/en-us/library/ms187819.aspx

This is the accuracy of datetime function in SQL.

You could probably use datetime2 if you are using a newer version of MSSQL as the accuracy for that is 100 nanosecs.

Datetime2: http://msdn.microsoft.com/en-us/library/bb677335.aspx

Alex Szabo
  • 3,274
  • 2
  • 18
  • 30
  • 4
    The question is why? – anar khalilov Sep 10 '14 at 07:59
  • 4
    @AnarKhalilov Because the `datetime` type has a finite precision and is not stored as a decimal number? :) Note that `datetime2` has a much bigger precision, for example. – Luaan Sep 10 '14 at 08:00
  • 1
    @AnarKhalilov this is no doubt related to the inaccuracy of floating point values. According to [this page](http://floating-point-gui.de/languages/sql/), the SQL standard prescribes to use IEEE754 double precision internally. – MarioDS Sep 10 '14 at 08:02
  • related: http://stackoverflow.com/q/1143259/1313143. Especially the comment on the answer!!! – MarioDS Sep 10 '14 at 08:03
  • @MDeSchaepmeester I assume this constraint must have been causing bugs in many applications. I mean isn't it a very basic thing? – anar khalilov Sep 10 '14 at 08:05
  • 1
    @AnarKhalilov I don't think accurate milliseconds are often needed in business software. Scientific software will probably not store them in a SQL database for reasons like this. – MarioDS Sep 10 '14 at 08:07
  • @MDeSchaepmeester Many financial and banking software use SQL Server. Is Oracle the same in this regard? – anar khalilov Sep 10 '14 at 08:09
  • While the inaccuracy in representation is understandable, all of the world rounds the result to nearest (or nearest lower) legal value, meaning in any sane system `20140904 23:59:59.999` would be rounded to `20140904 23:59:59.997`. The fact that if you're 2ms off from nearest legal value your result can be rounded by up to 86400000 milliseconds down is some kind of sick aberration. – SF. Sep 10 '14 at 08:14
  • @SF what do you mean? 20140905 00:00:00.000 *IS* the closest legal value to 20140904 23:59:59.999 with just a 1 ms difference. – Peteris Sep 10 '14 at 09:21
  • @Peteris: Ooops. Didn't notice the day change. – SF. Sep 10 '14 at 09:29
3

The increments are part of the API spec: See this link.

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

I found this link which has at the end a better explanation. Quote:

Actually, SQL Server does store there the clock-ticks since midnight. Each clock-tick is equivalent to 3.33 milliseconds. That’s also the reason why the DATETIME datatype has an accuracy of one three-hundredth of a second.

DATETIME is 2 x 4 bytes (date + time). The time part is stored as number of ticks since midnight. Each tick is 3.33 ms

If you need more precision, take a look at DATETIME2 type. DATETIME2 Accuracy is 100ns.

TT.
  • 15,774
  • 6
  • 47
  • 88
  • Apparantly DATETIME2 is also ISO 8601 compliant, which might be useful in some scenarios. – TT. Sep 10 '14 at 08:16