Convert string into date in SQL Server

Question

Conversion failed when converting date and/or time from character string.

I'm getting the above error when running this statement in SQL Server:

SELECT CONVERT(datetime, 'Fri, 15 Jan 2016 17:30:05 GMT')

Actually I want to insert same string format in Datetime column

Answer 1

As suggested by Tim Biegeleisen, that string needs to be processed to be converted. In order to convert it you need to strip of the day (Fri,) and the GMT timezone at the end, for example:

DECLARE @date varchar(50) = 'Fri, 15 Jan 2016 17:30:05 GMT'
SELECT  CONVERT(DATETIME, SUBSTRING(@date, 5, LEN(@date) - 8), 113)

This solution does strip the timezone information, have a look at this post if you want to convert it back to UTC.

Answer 2

If you want to insert the string 'Fri, 15 Jan 2016 17:30:05 GMT' into datetime column then you need to remove Fri, and GMT from the string before inserting.

SELECT CAST(substring(@str_date,5,len(@str_date)-8) as datetime)

DEMO