SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table ref_timezones (
 tz_code varchar(8) primary key, 
 tz_offset char(6) not null check (tz_offset like '[+-][01][0-9]:[0-9][0-9]'), 
 name varchar(30)
);
insert into ref_timezones (
tz_code, 
tz_offset
) values
('CDT',  '-05:00'),
('EST',  '-05:00'),
('Z',    '+00:00'),
('GMT',  '+00:00'),
('+00:00', '+00:00'),
('CET',  '+01:00'),
('CEST', '+02:00'), 
('ACDT', '+10:30');
8 rows affected

create table test (col varchar(30));
insert into test (col) values
  ('Tue, 07 Dec 2021 04:35:05 GMT')
, ('Wed, 08 Dec 2021 05:46:36 CEST')
2 rows affected

SELECT col
, TRY_CAST(SUBSTRING(col,6,20) AS DATETIME) AS dt_no_offset
, CAST(SWITCHOFFSET(
    TRY_CAST(
      SUBSTRING(col, PATINDEX('%[0-9]%', col), 21)
    + COALESCE(tz.tz_offset, '+00:00')
    AS DATETIMEOFFSET(0)), '+00:00') AS SMALLDATETIME) AS small_dt_at_zulu
FROM test
OUTER APPLY (
   SELECT tz_offset
   FROM ref_timezones 
   WHERE tz_code = RIGHT(col, PATINDEX('%_[ ][0-9][0-9]:%', REVERSE(col)))
) tz;


col
dt_no_offset
small_dt_at_zulu


Tue, 07 Dec 2021 04:35:05 GMT
2021-12-07 04:35:05.000
2021-12-07 04:35

Wed, 08 Dec 2021 05:46:36 CEST
2021-12-08 05:46:36.000
2021-12-08 03:47

col	dt_no_offset	small_dt_at_zulu
Tue, 07 Dec 2021 04:35:05 GMT	2021-12-07 04:35:05.000	2021-12-07 04:35
Wed, 08 Dec 2021 05:46:36 CEST	2021-12-08 05:46:36.000	2021-12-08 03:47