MS SQL Converting a String to Datetime issue

Question

I'm trying to convert the string to datetime, but getting an unexpected result.

Select Convert(DateTime, '2015-08-10 13:08:01.725', 121);

Result:

2015-08-10 13:08:01.727

Note that the milliseconds have changed from 725 to 727.

I'm using Microsoft SQL Server 2014 (SP3-CU2) (KB4482960) - 12.0.6214.1 (X64).

This [Q&A](https://stackoverflow.com/questions/634122/milliseconds-wrong-when-converting-from-xml-to-sql-server-datetime) explains a similar issue. — Zhorov, Jul 30 '21 at 08:11
@AmitVerma The expected result is that the milliseconds show as 725, rather than 727. I've edited the question to clarify that. — Tim, Jul 30 '21 at 08:26

score 0 · Answer 1 · answered Jul 30 '21 at 08:34

0

This is because DATETIME is rounded to increments of .000, .003, or .007 seconds

Directly from Microsoft documentation

answered Jul 30 '21 at 08:34

Otter

1,086
7
18

score 0 · Accepted Answer · answered Jul 30 '21 at 08:35

This is expected, datetime is only accurate to 1/300 of a second, and .725 can't be represented by that. As a result it's rounded to the nearest 1/300, which would be .726666666666~, and then displayed as .727 as the display layer is accurate to 3 digits.

If you want to be accurate to 1/1000 of a second, use a more precise data type. datetime2(3) seems to be what you are after:

SELECT CONVERT(datetime2(3), '2015-08-10 13:08:01.725', 121);

MS SQL Converting a String to Datetime issue

2 Answers2