How to calculate local time based on GMT?

Question

I am trying to figure out the local time for various countries based on the GMT time data I have for them. I would also need to know if any of them observe Daylight Savings Time as well. Is there a way to achieve this without CLRs? Here is some sample data for your reference.

DDL:

CREATE TABLE #Temp(
COUNTRY_CODE VARCHAR(2)
DATETIME VARCHAR(50)
);

DML:

INSERT INTO #Temp(COUNTRY_CODE, DATETIME)
VALUES
('EE', 'Sun Mar 29 00:00:00 GMT 1992'),
('AZ', 'Sun Mar 28 00:00:00 GMT 1993'),
('CZ', 'Sun Mar 27 00:00:00 GMT 1994'),
('DE', 'Sun Mar 26 00:00:00 GMT 1995'),
('AD', 'Sun Mar 31 00:00:00 GMT 1996'),
('GI', 'Sun Mar 30 00:00:00 GMT 1997'),
('DK', 'Sun Mar 29 00:00:00 GMT 1998'),
('AL', 'Sun Mar 28 00:00:00 GMT 1999'),
('ES', 'Sun Mar 26 00:00:00 GMT 2000'),
('LT', 'Sun Mar 25 00:00:00 GMT 2001');

Environment: Microsoft Azure SQL Data Warehouse - 10.0.10783.0 Oct 26 2019 23:24:02 Copyright (c) Microsoft Corporation

Thanks!

Answer 1

This is messy, because your data is. You have a long road ahead of you, and i suggest actually changing your "DateTime" column to the correct data type, not using a computed column:

--Create sample table
CREATE TABLE dbo.YourTable (CountryCode char(2),
                            NotADateTime varchar(50));

INSERT INTO dbo.YourTable (CountryCode,
                           NotADateTime)
VALUES ('EE', 'Sun Mar 29 00:00:00 GMT 1992'),
       ('AZ', 'Sun Mar 28 00:00:00 GMT 1993'),
       ('CZ', 'Sun Mar 27 00:00:00 GMT 1994'),
       ('DE', 'Sun Mar 26 00:00:00 GMT 1995'),
       ('AD', 'Sun Mar 31 00:00:00 GMT 1996'),
       ('GI', 'Sun Mar 30 00:00:00 GMT 1997'),
       ('DK', 'Sun Mar 29 00:00:00 GMT 1998'),
       ('AL', 'Sun Mar 28 00:00:00 GMT 1999'),
       ('ES', 'Sun Mar 26 00:00:00 GMT 2000'),
       ('LT', 'Sun Mar 25 00:00:00 GMT 2001'),
       ('FR', 'Sun Jul 14 00:00:00 GMT 2019'); --Add one that is in DST.
GO
--Create a proper datetimeoffset column
ALTER TABLE dbo.YourTable ADD ADateTime AS TRY_CONVERT(datetimeoffset(0),RIGHT(NotADateTime,4) + '-' +
                                                                         CASE SUBSTRING(NotADateTime,5,3) WHEN 'Jan' THEN '01'
                                                                                                             WHEN 'Feb' THEN '02'
                                                                                                             WHEN 'Mar' THEN '03'
                                                                                                             WHEN 'Apr' THEN '04'
                                                                                                             WHEN 'May' THEN '05'
                                                                                                             WHEN 'Jun' THEN '06'
                                                                                                             WHEN 'Jul' THEN '07'
                                                                                                             WHEN 'Aug' THEN '08'
                                                                                                             WHEN 'Sep' THEN '09'
                                                                                                             WHEN 'Oct' THEN '10'
                                                                                                             WHEN 'Nov' THEN '11'
                                                                                                             WHEN 'Dec' THEN '12'
                                                                         END + '-' + SUBSTRING(NotADateTime,9,2) + 'T' +
                                                                         SUBSTRING(NotADateTime,12,9));

GO
--Check data
SELECT YT.CountryCode,
       YT.NotADateTime,
       YT.ADateTime
FROM dbo.YourTable YT;
GO     
--Add a timezone lookup table
CREATE TABLE dbo.TimeZoneLookup (CountryCode char(2),
                                 MSTimeZoneName nvarchar(128));
INSERT INTO dbo.TimeZoneLookup (CountryCode,
                                MSTimeZoneName)
SELECT CountryCode,'Central Europe Standard Time' --All apepar to be european countries, so jsut assumed. You should NOT. 
FROM dbo.YourTable; --You can find all the timezones in sys.time_zone_info


GO
--JOIN and change timezone
SELECT YT.CountryCode,
       YT.ADateTime,
       YT.ADateTime AT TIME ZONE TZ.MSTimeZoneName
FROM dbo.YourTable YT
     JOIN dbo.TimeZoneLookup TZ ON YT.CountryCode = TZ.CountryCode

GO
--Clean up
DROP TABLE dbo.YourTable;
DROP TABLE dbo.TimeZoneLookup;