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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy