Convert 16-digit datetime string to datetime with microseconds-CodePudding

I am trying to convert a 16-digit datetime string (with milliseconds) in SQL Server to an actual datetime along with the milliseconds. An example would be changing a record like this, 2022020804180454, to read a US DateTime of 02/08/2022 04:18:04.54.

How do I go about doing this? Google/Bing searches speak to having the date with a midnight timestamp

CONVERT(datetime, CAST('2022020804180454' AS CHAR(8)), 101)

which is not what I am after.

Any help or being pointed in the right direction would be helpful.

CodePudding user response：

You can use the handy DATETIMEFROMPARTS:

DECLARE @s nvarchar(16) = '2022020804180454';


SELECT DATETIMEFROMPARTS(LEFT(@s,4), --year
                         SUBSTRING(@s,5,2), --month
                         SUBSTRING(@s,7,2), --day
                         SUBSTRING(@s,9,2), --hour
                         SUBSTRING(@s,11,2), --minute
                         SUBSTRING(@s,13,2), --second
                         SUBSTRING(@s,15,2)*10 --millisecond
                         )

CodePudding user response：

Inject the needed characters, with STUFF, and then convert:

SELECT CONVERT(datetime2(2),STUFF(STUFF(STUFF(STUFF(V.YourDateString,15,0,'.'),13,0,':'),11,0,':'),9,0,' '))
FROM (VALUES('2022020804180454'))V(YourDateString);