Convert to datetime producing incorrect results

Asked Feb 13 '20 at 21:05

Active Feb 13 '20 at 21:13

Viewed 26 times

I am trying to convert a varchar column value into datetime datatype, and I am not getting the expected result.

SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.214') UNION ALL 
SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.215') UNION ALL
SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.216') UNION ALL 
SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.217') UNION ALL 
SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.218') UNION ALL 
SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.219') UNION ALL 
SELECT CONVERT(DATETIME, '2020-01-09 10:22:45.220')

I am getting the following results:

2020-01-09 10:22:45.213
2020-01-09 10:22:45.217
2020-01-09 10:22:45.217
2020-01-09 10:22:45.217
2020-01-09 10:22:45.217
2020-01-09 10:22:45.220
2020-01-09 10:22:45.220

I have tried running this code in both SSMS 2014 and 2017 with the same result. I'm not sure what I am doing wrong here...

edited Feb 13 '20 at 21:13

marc_s

732,580
175
1,330
1,459

asked Feb 13 '20 at 21:05

Jennifer Dieckman

2

`datetime2` is now the recommended datetime datatype so try `DATETIME2(3)` – Dale K Feb 13 '20 at 21:08
2

Using ``datetime2`` fixes it. – nickfinity Feb 13 '20 at 21:08

Convert to datetime producing incorrect results

0 Answers0