Timezone codes

• tom 69406

  SSC-Addicted

  Points: 446

  More actions

  September 17, 2018 at 4:57 am

  #372790

  I have a set of data to convert to datetime
  Its in strings of the form 'dd/MM/yyyy HH:mm:ss ZZZ'
  Where ZZZ is the timezone code 
  e.g
  '24/01/2018 20:24:42 GMT'
  '17/06/2018 11:32:27 BST'

  I can convert the datetimes easily enough with CONVERT(DateTime, Substring(VALUE,1, 20), 103) - its the 3-letter timezone abbreviations I need help with
  sys.time_zone_info does not have the 3-letter abbreviations - though some of them feature as part of the name

  Is there any built-in Sql Server function or table or feature to convert these timezone abbreviations to +/- hours from UTC?
  Or will I have to write my own.

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  September 20, 2018 at 8:25 pm

  #2006510

  Alas you have to build your own table to map the codes onto the zone names, then you can use AT TIME ZONE to convert the actual times, i.e.

  Declare @TimeZoneCodes Table(ZoneCode VarChar(10) Primary Key, ZoneName VarChar(100))
Insert Into @TimeZoneCodes
Values ('GMT','Greenwich Standard Time'),('BST','GMT Standard Time')
Select
  *,
  CONVERT(DateTime, Substring(DateString,1, 20), 103) At Time Zone ZoneName,
   (CONVERT(DateTime, Substring(DateString,1, 20), 103) At Time Zone ZoneName) At Time Zone 'UTC'
  From (Values
  ('24/01/2018 20:24:42 GMT'),
  ('17/06/2018 11:32:27 BST')
  ) As T(DateString)
  Cross Apply
  (
  Select
    Trim(Substring(DateString, 20,Len(DateString)))
  ) CA(ZoneCode)
  Join @TimeZoneCodes TZ On TZ.ZoneCode = CA.ZoneCode

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  September 21, 2018 at 12:23 pm

  #2006624

  My suggestion would be to create a "Time Zone Abbreviations" table and then create an inline table valued function to sit on top of that...
  Something like the following...
  CREATE TABLE dbo.time_zone_abbreviations (
    Abbreviation VARCHAR(5) NOT NULL
        CONSTRAINT pk_tza PRIMARY KEY CLUSTERED,
    time_zone_name VARCHAR(255) NOT NULL,
    --global_location VARCHAR(100) NOT NULL,
    UTC_offset_hours DECIMAL(9,2) NOT NULL
  );
GO

  INSERT dbo.time_zone_abbreviations (Abbreviation, time_zone_name, UTC_offset_hours) VALUES
        ('ADT', 'Atlantic Daylight Time', -3),
        ('AKDT', 'Alaska Daylight Time', -8),
        ('AKST', 'Alaska Standard Time', -9),
        ('AST', 'Atlantic Standard Time', -4),
        ('BST', 'British Summer Time', 1),
        ('CDT', 'Central Daylight Time', -5),
        ('CST', 'Central Standard Time', -6),
        ('EDT', 'Eastern Daylight Time', -4),
        ('EGST', 'Eastern Greenland Summer Time', 0),
        ('EGT', 'East Greenland Time', -1),
        ('EST', 'Eastern Standard Time', -5),
        ('GMT', 'Greenwich Mean Time', 0),
        ('HDT', 'Hawaii-Aleutian Daylight Time', -9),
        ('HST', 'Hawaii Standard Time', -10),
        ('MDT', 'Mountain Daylight Time', -6),
        ('MST', 'Mountain Standard Time', -7),
        ('NDT', 'Newfoundland Daylight Time', -2.5),
        ('NST', 'Newfoundland Standard Time', -3.5),
        ('PDT', 'Pacific Daylight Time', -7),
        ('PMDT', 'Pierre & Miquelon Daylight Time', -2),
        ('PMST', 'Pierre & Miquelon Standard Time', -3),
        ('PST', 'Pacific Standard Time', -8),
        ('WGST', 'Western Greenland Summer Time', -2),
        ('WGT', 'West Greenland Time', -3);
GO

  -- SELECT * FROM dbo.time_zone_abbreviations tza;

  --=======================================================================

  -- DROP FUNCTION dbo.tfn_UTC_TimeZone_Adjust;

  SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_NULLS ON;
GO

  CREATE FUNCTION dbo.tfn_UTC_TimeZone_Adjust
/* ===================================================================
09/21/2018 JL, Created: Converts a date time string in the format of
            dd/MM/yyyy HH:mm:ss ZZZZZ to an actual DATETIME data type
            and returns specific time zone information.    
=================================================================== */
--===== Define I/O parameters
(
    @time_string VARCHAR(25)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
    SELECT
        pv.DateTime_Value,
        UTC_DateTime = DATEADD(HOUR, tza.UTC_offset_hours, pv.DateTime_Value),
        tza.Abbreviation,
        tza.time_zone_name,
        tza.UTC_offset_hours
    FROM
        ( VALUES (
                CONVERT(DATETIME, SUBSTRING(@time_string, 1, 20), 103),
                LTRIM(RTRIM(SUBSTRING(@time_string, 21, 5)))
                ) ) pv (DateTime_Value, TimeZoneCode)
        JOIN dbo.time_zone_abbreviations tza
            ON pv.TimeZoneCode = tza.Abbreviation;
GO

  Once you have those two objects in place your coding becomes as simple as this...
  IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
BEGIN DROP TABLE #TestData; END;
GO

  CREATE TABLE #TestData (
    TimeString VARCHAR(25) NOT NULL
    );
GO

  INSERT #TestData (TimeString) VALUES
        ('24/01/2018 20:24:42 GMT'),
        ('17/06/2018 11:32:27 BST');
GO
--------------------------------------------------------------

  SELECT
    *
FROM
    #TestData td
    CROSS APPLY dbo.tfn_UTC_TimeZone_Adjust(td.TimeString) utza;
GO

  Results:
  TimeString ------------ DateTime_Value -------- UTC_DateTime --------- Abbreviation time_zone_name -------- UTC_offset_hours
----------------------- ----------------------- ----------------------- ------------ ----------------------- ------------------
24/01/2018 20:24:42 GMT 2018-01-24 20:24:42.000 2018-01-24 20:24:42.000 GMT          Greenwich Mean Time     0.00
17/06/2018 11:32:27 BST 2018-06-17 11:32:27.000 2018-06-17 12:32:27.000 BST          British Summer Time     1.00