Format specific datetime string to datetime datatype in SQL Server

I have a string which stands for a specific format of datetime.
Format: yyyymmddhhmmss
For example: 20220504111621

I want it to convert to a SQL Server datetime datatype: yyyy-mm-dd hh:mm:ss (Format 120)

Is there a way to do that?
I tried something like:

select [SYNC_TIME] 
replace(convert(varchar, [SYNC_TIME],101),'/','') + replace(convert(varchar, [SYNC_TIME],108),':','')

FROM [DB].[dbo].[DATABASENAME]

>Solution :

Lots of ways, you could use stuff to format the string and convert:

declare @string varchar(15)='20220504111621'
select Convert(datetime,Stuff(Stuff(Stuff(@string,9,0,' '),12,0,':'),15,0,':'),112)

Leave a Reply