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!

Why are you storing a date and time as a `varchar` at all? And the format you have is an awful one to deal with. Store the times as a Date and Time datatype and then use [`AT TIME ZONE`](https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver15) or [`SWITCHOFFSET`](https://learn.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver15). — Thom A, Dec 06 '19 at 14:47
Note, as well, that some countries (such as Germany (DE) and Spain (ES) in your example) use Day Light Saving, meaning that they change offset. By GMT, I *assume* you also mean UTC, rather than the time in the UK (which too, uses DST, so times in the summer are UTC +01:00, not UTC). — Thom A, Dec 06 '19 at 14:54
+10 to all of Larnu's points. In addition, the table is neither needed nor likely to work. Countries don't have a fixed offset, they have *multiple* offsets and DST rules. `AT TIME ZONE` already takes care of them. SQL Server 2016+ already has a table of timezones. The complain everyone has is that it uses Windows timezone names instead of the de-facto standard, IANA timezone names — Panagiotis Kanavos, Dec 06 '19 at 14:54
And why do you post the same question independently to multiple forums? Like [msdn](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/231ad73c-ac79-4cd5-a67f-22f46b827476/how-to-calculate-local-time-based-on-gmt?forum=transactsql) — SMor, Dec 06 '19 at 15:37
What is orchestrating your data loads? In the past I have had to deal with timezone translation in Azure DW where daylight savings is in play. I had SSIS on a VM in the architecture so calculated the current local time on the VM and set it in a Azure DW metadata table for use downstream loads. — GregGalloway, Dec 08 '19 at 16:32
@GregGalloway Would you be able to share any code snippets for the same? Thanks! — Julaayi, Dec 10 '19 at 14:36
@Julaayi `select getdate()` on the VM (assumes VM is in right timezone) and copy that value to the DW into a table or variable. Then do `datediff(minute,@DateFromVM,getdate())` in the DW to get the current minutes offset to UTC. — GregGalloway, Dec 11 '19 at 00:07

score 0 · Answer 1 · answered Dec 06 '19 at 15:47

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;

I am getting the dates from a Legacy application and I have no control over it. So, I had to process it accordingly before feeding it into my DW. — Julaayi, Dec 10 '19 at 14:36

How to calculate local time based on GMT?

1 Answers1