Convert string timestamp in format yyyy-MM-ddTHH:mm:ss.ffffffZ to SQL Server datetime without changi-CodePudding

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.

CodePudding user response：

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

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

CodePudding user response：

@Vvdl already wrote an answer explaining how to solve this. I want to supplement that by providing references to the relevant parts of the documentation.

Allowed formats for datetime are documented here: Supported string literal formats for datetime. The section on ISO 8601 lists the following format:

YYYY-MM-DDThh:mm:ss[.mmm]

datetime2, on the other hand, supports more decimal places: Supported string literal formats for datetime2

YYYY-MM-DDThh:mm:ss[.nnnnnnn]

In the examples, the documentation further explains that "...[t]he time zone is truncated...", which explains why the trailing Z is allowed, even though it does not match the format described above.