user5493187 user5493187 - 5 months ago 12
SQL Question

Convert unusual string format to Datetime in SQL

I receive dates in this format:

Wed Jun 29 2016 15:57:45 GMT 0100 (GMT Daylight Time)


What is the best way to either convert that string to
datetime
as-is, or cut it down enough to be converted (e.g.
Jun 29 2016 15:57:45
) without the risk of losing data?

Answer

Use Substring and Charindex string functions to trim the datetime

Try this

declare @date_str varchar(100) = 'Wed Jun 29 2016 15:57:45 GMT 0100 (GMT Daylight Time)'

select cast(substring(@date_str,charindex(' ',@date_str),charindex('GMT',@date_str)-charindex(' ',@date_str)) as datetime)

Result : 2016-06-29 15:57:45.000

Comments