CODEWITHSUNDEEP
sql-server
Manu
Manu

Reputation: 819

Why does the function CONVERT round the result?

When I execute GETDATE in SQL Server, I get, for example:

2021-01-29 17:07:56.319

But when I put it in a CONVERT function, it round the result, like :

SELECT CONVERT(datetime, '2021-01-29 17:07:56.319', 121);

returns

'2021-01-29 17:07:56.320'

Why?

While

SELECT CONVERT(datetime2, '2021-01-29 17:07:56.319');

returns the expected result:

'2021-01-29 17:07:56.319'

My SQL Server is 2017 (v14.0.1000).

Thanks

Upvotes: 1

Views: 54

Answers (1)

Thom A
Thom A

Reputation: 95621

It has nothing to do with CONVERT, and to do with your choice of data type. Per it's documentation:

Accuracy: Rounded to increments of .000, .003, or .007 seconds

If you want your value to be accurate to 1/1000 of a second, use a data type that can have that accuracy.

SELECT CONVERT(datetime2(3), '2021-01-29 17:07:56.319', 121);

Side note: SQL Server version 14.0.1000(.169 I assume) is SQL Server 2017 RTM. You haven't updated your version of SQL Server for 4 years! Open up Windows Update and get that instance to supported version!

Upvotes: 5

Related Questions