3

Why does

SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME)

return:

2014-11-28 15:06:02.167
1
8

DateTime has a precision of has a 0.00333(1/300th) seconds, so sql server rounds value to nearest 1/300th.

If you are using SQL Server 2008 above, use DateTime2 which can have a precision of millisecond, even down to 100 nanoseconds and can hold the accurate value.

SELECT CAST('2014-11-28 15:06:02.165' AS DATETIME2(3));
4
  • Thanks, that's a little surprising. I would never have expected the value to change between string and DATETIME. I wouldn't be surprised if it has messed up a few systems. Nov 28 '14 at 10:10
  • 1
    @Dmaien, it's not changed, but truncated to match the datatype accuracy. If you had cast the string to DATE, would you expect the hours and minutes to remain? Nov 28 '14 at 14:32
  • @DamienGolding what would you expect SELECT CAST('3.6666666666666666' AS float) to return?
    – AakashM
    Nov 28 '14 at 15:38
  • @ypercube Obviously I would expect truncation to happen with DATE but the confusing thing with DATETIME is that it returns ms in a format down to 1 ms but can't represent them all. Does it have a practical purpose? In other words, why not limit to 0.01s? Nov 29 '14 at 0:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.