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.

CodePudding user response：

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.

CodePudding user response：

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;

db<>fiddle here