Using convert in SQL Server returns a wrong number of milliseconds

Question

Why in SQL Server if I do this:

SELECT CONVERT(datetime, '2020-06-23 23:21:22.302', 121)

I get this date:

2020-06-23 23:21:22.303

Try `datetime2(3)` because `datetime` can't store/represent 302 milliseconds. — Aaron Bertrand, Jun 24 '20 at 00:51

score 5 · Accepted Answer · answered Jun 23 '20 at 23:41

5

see this thread

Its because datetime in SQL Server is only accurate to 3ms and will round to increments of of .000, .003, or .007 seconds

answered Jun 23 '20 at 23:41

cpalmer

311
2
9

score 2 · Answer 2 · answered Jun 23 '20 at 23:47

2

Try this:

SELECT convert(datetime, '2020-06-23 23:21:22.302', 121)
SELECT convert(datetime2, '2020-06-23 23:21:22.302', 121)
SELECT convert(datetime2(3), '2020-06-23 23:21:22.302', 121)

answered Jun 23 '20 at 23:47

Rodney Ellis

727
2
13
28

Using convert in SQL Server returns a wrong number of milliseconds

2 Answers2