Search code examples
sql-serverdatetimeutc

Convert string timestamp in format yyyy-MM-ddTHH:mm:ss.ffffffZ to SQL Server datetime without changing time zone

I would like to transform a UTC timestamp with 5-6 ms digits into a SQL Server datetime. I do not want to localize or anything, just want to keep UTC but turn it into a datetime

e.g.

declare @utc_timestamp varchar(20) = '2022-10-05T13:12:02.000402Z';

-- Desired datetime format (is this even possible or does this not conform to SQL Server's datetime format?
2022-10-05 13:12:02.000402

Interestingly, this works:

declare @utc_timestamp varchar(20) = '2022-10-05T13:12:22Z';
select cast(@utc_timestamp as datetime)

-- result
2022-10-05 13:12:22.000

So it leads me to believe that SQL Server's datetime can only parse a UTC timestamp if it doesn't have too many ms digits.

Solution

  • You can use datetime2 for this, it has a higher precision.

    select cast('2022-10-05T13:12:02.000402Z' as datetime2)