How to Converting string 'Tue, 07 Dec 2021 04:35:05 GMT' to smalldatetime (yyyy-mm-dd hh:mm:ss) in sql server

Question

I have string 'Tue, 07 Dec 2021 04:35:05 GMT' and i need to convert to smalldatetime format like '2021-12-07 04:35:05'(yyyy-mm-dd hh:mm:ss) in sql server. please help. Thanks in advance.

Answer 1

If you truncate the day name and the timezone from the value, and you're using an English based language, this works. I assume your value is always in the format ddd, dd MMM yyyy hh:mm:ss tz:

SELECT TRY_CONVERT(smalldatetime, SUBSTRING('Tue, 07 Dec 2021 04:35:05 GMT',6,20);

Note that smalldatetime is accurate to 1 minute, so the seconds precision will be lost. So, for your value, 2021-12-07 04:35:00 would be returned.

Answer 2

Just in case that the datetime string isn't only in the GMT timezone.
And if you'd like to have a small datetime in a fixed timezone.

Here's a demo that makes use of a reference table.

Sample data

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');

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')

Query

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

db<>fiddle here