I have a field that is formatted as "Tue, 22 Aug 2017 03:06:47 GMT" that I am pulling out of an XML file. I would like to insert this into a SQL database as a datetime value. Can anyone please guide me on the syntax for the SQL insert?

This question probably belongs in a sql server forum. You can have a look at the example below, but note that if you need some kind of timezone conversion, then the below query won't work

WITH temp AS (
SELECT 'Tue, 22 Aug 2017 03:06:47 GMT' as dt
)
,temp2 AS
(
SELECT dt, RTRIM(LTRIM(REPLACE(SUBSTRING(dt, charindex(',', dt) + 1, 100), 'GMT',''))) AS TrimmedDate
FROM temp
)

SELECT dt, TrimmedDate, CONVERT(DATETIME2, trimmeddate, 13) as FinalDate
FROM temp2

Thank you very much for this Thomas. I really appreciate it.

Please forgive my ignorance, but if I wanted to insert this date into the database, would I need to change the "Select" to "Insert" in your example?

if it is one row you are inserting, you could do something like this:

DECLARE @dtStr AS VARCHAR(30) = 'Tue, 22 Aug 2017 03:06:47 GMT';
DECLARE @dt AS DATETIME2;

SET @dt = CONVERT(DATETIME2, RTRIM(LTRIM(REPLACE(SUBSTRING(@dtStr, CHARINDEX(',', @dtStr)+1, LEN(@dtStr)), 'GMT', ''))), 13);

INSERT INTO SomeTable(YourDateColumn)
VALUES (@dt)

Thank you SO much Thomas...this worked perfectly! I cant tell you how much I appreciate your help...very kind of you.

Hi Thomas,

I have a slight variance of the date source and wanted to see if you might be able to tell me how to update my SQL to accommodate it. I have tried a few things but cant seem to get it right.

The date format is 'Sun, 10 Sep 2017 15:10:42 -0700'

I am not sure what the -0700 represents, so not sure if it will change. I would assume that the length of that string wont change much.

Any help is greatly appreciated.

If you just want to get rid of the latter part, and the string length is fixed, this works:

DECLARE @dtStr AS VARCHAR(30) = 'Sun, 10 Sep 2017 15:10:42 -0700';
DECLARE @dt AS DATETIME2;

SET @dt = CONVERT(DATETIME2, SUBSTRING(@dtStr, 6, 20), 13);


The last part '-0700' is an offset in hours to UTC. If you want to take this into account when converting your date, you should ask on StackOverflow. Timezones are not my speciality.

Thank you so much Thomas...this worked great! You are very helpful and I really appreciate it.

I will look into the time zones...thanks for pointing me in that direction.