RFC2822 datetime format to a usable datetime in SQL server

Question

I am receiving a datetime in the following format:

Thu, 18 Mar 2021 10:37:31 +0000

If I'm correct this if RFC2822.

I can't figure out a way to convert this to a 'normal' datetime that would be used by SQl server. For example I want it to be:

2021-03-18 10:37:31
YYYY-MM-DD hh:mi:ss

I have tried things like CONVERT() and found a sketchy way by doing:

DECLARE @WeirdDate varchar(50) = 'Thu, 30 Jul 2015 20:00:00 +0000'
SELECT
CONVERT(DATETIME, SUBSTRING(@WeirdDate, CHARINDEX(',', @WeirdDate) + 1, 20))

But none of it is working that well. Is there a way to convert this in a 'proper' way?

edit:

To clarify: The format should always be the same as the provided example. Including the day name.

I am not sure that it will always be the same timezone. I could be receiving it from a different timezone. This is something to consider.

Answer 1

You could achieve this with a "little" string manipulation is seems, and some style codes:

DECLARE @YourDate varchar(50) = 'Thu, 30 Jul 2015 20:00:00 +0000'
SELECT TRY_CONVERT(datetimeoffset(0),CONVERT(varchar(25),TRY_CONVERT(datetime2(0),STUFF(STUFF(@YourDate,1,5,''),21,6,''),106),126) + STUFF(RIGHT(@YourDate,5),4,0,':'));

This will, however, fail if you're using a LOGIN with a language setting which isn't an English based one.

If the value is always UTC, you can actually just use the "middle" TRY_CONVERT expression.

Answer 2

In my case the string comes with extra information

Mon Feb 15 2021 15:48:09 GMT-0500 (Colombia Standard Time)
Sun Mar 28 2021 09:59:51 GMT-0500 (hora de Ecuador)
Fri Jun 25 2021 08:54:28 GMT-0500 (Ecuador Time)

I used some replace functions to get rid of the extra information

TRY_PARSE( replace( replace( replace( replace( <<date_field>>, '(Colombia Standard Time)', '' ), 'GMT', '' ), '(hora de Ecuador)', ''), '(Ecuador Time)', '' ) as datetime using 'en-US')