Convert String to Datetime format

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 2236
We have a string value as 07052018080504623

It represents MM DD YYYY HH MM SS MS

means

MM = 07, DD = 05, YYYY=2018, HH = 08, MM=05 , SS= 04, MS = 623

The desired output should be a datetime format.

The below script will do so

declare @var as varchar(20) ='07052018080504623'
select
cast(
SUBSTRING(@var, 1, 2) -- Month
+ '/'
+ SUBSTRING(@var, 3, 2) -- Day
+ '/'
+ SUBSTRING(@var, 5, 4) --Year
+ ' '
+ SUBSTRING(@var, 9, 2) -- Hour
+':'
+ SUBSTRING(@var, 11, 2) -- Minute
+':'
+ SUBSTRING(@var, 13, 2) -- Second
+'.'
+ SUBSTRING(@var, 15, 3) -- Millisecond
as datetime) as DateTimeFormat

Comments or Responses

Login to post response