user5493187 user5493187 - 1 year ago 72
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
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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download