1

I'm trying to convert a string to datetime like this select CONVERT(datetime, '31-05-2022 04:00:00.105', 105) but the precision of the milliseconds changes. How is it possible ?

In my case it gives me 2022-05-31 04:00:00.107

Thanks for your help.

I_G
  • 413
  • 1
  • 4
  • 18
  • See [Rounding of datetime fractional second precision](https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16#rounding-of-datetime-fractional-second-precision). – HABO Jun 01 '22 at 18:37

2 Answers2

2

Datetime is only accurate to 3.33 milliseconds. If you try to wedge a value in that is more precise than that, MS SQL will round to the near acceptable value. More information is available here:

Milliseconds in my DateTime changes when stored in SQL Server

Robert Sievers
  • 1,277
  • 10
  • 15
2

You could try the 'datetime2' (or 'datetimeoffset') column type to account for the greater precision required

select CONVERT(datetime2, '31-05-2022 04:00:00.105', 105)
SteveC
  • 5,955
  • 2
  • 11
  • 24