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

What is the expected result. Do you want to convert to local time — Pரதீப், Jan 16 '16 at 07:14
The format of your current timestamp does not appear to be supported by `CONVERT`, so I think you will need to process it to get the conversion to work. — Tim Biegeleisen, Jan 16 '16 at 07:15
actually i want to insert same string format in Datetime column — Shakeel Hussain Mir, Jan 16 '16 at 07:19
Present your data to SQL Server in a form that it can easily process: the [ISO 8601 format](https://en.wikipedia.org/wiki/ISO_8601). — TT., Jan 16 '16 at 08:13

score 3 · Answer 1 · answered Jan 16 '16 at 07:22

3

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

answered Jan 16 '16 at 07:22

Pரதீப்

91,748
19
131
172

score 3 · Accepted Answer · edited May 23 '17 at 12:23

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.

Convert string into date in SQL Server

2 Answers2