-2

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.

Shankar
  • 29
  • 2
  • 6
  • Why do you have code that converts datetimes to non-ISO 8601 format from the start? (Don't answer this, just consider correcting this somewhere else.) – Danny Varod Jan 19 '22 at 14:35
  • 2
    This makes me think you're making one of two very big mistakes. Either 1) you're formatting DateTime values for storage in a varchar column (_NEVER_ do that; always use DateTime/DateTime2/etc for your date values) or you're trying to use string concatenation instead of parameterized queries to build your SQL, which... eww. That's the fast way to get your app hacked. – Joel Coehoorn Jan 19 '22 at 14:49
  • You have a fine collection of tags there. Are you writing this code in spark and it's being pushed to SQL Server? Where will this code be written? – Nick.Mc Jan 20 '22 at 04:59

2 Answers2

0

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
0

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

LukStorms
  • 28,916
  • 5
  • 31
  • 45