Why does

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


2014-11-28 15:06:02.167

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));
  • 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. – Damien Golding 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? – ypercubeᵀᴹ 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? – Damien Golding Nov 29 '14 at 0:33

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