September 17, 2018 at 4:57 am
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.
September 20, 2018 at 8:25 pm
Declare @TimeZoneCodes Table(ZoneCode VarChar(10) Primary Key, ZoneName VarChar(100))Select
Insert Into @TimeZoneCodes
Values ('GMT','Greenwich Standard Time'),('BST','GMT Standard Time')
*,
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
September 21, 2018 at 12:23 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply