Querying Timezones in MS SQL

Home / Querying Timezones in MS SQL

If you’ve read my various posts on dealing with datetimes and timezones lately, I discovered a handy little mechanism for dealing with timezones in Microsoft SQL.


Windows stores all Timezone information in the Registry. Fortunately, Microsoft SQL, through T-SQL, can access and query the registry. Below is a simple query that will pull all of the timezone information from the registry and put it all into a temporary table. The timezone offset is determined by performing a split/parse of the offset string from the registry. This information can be used to convert between zones.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
SET NOCOUNT ON;
 
DECLARE @root varchar(1000) = 'HKEY_LOCAL_MACHINE';
DECLARE @key varchar(1000) = 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones';
DECLARE @tzName varchar(1000);
DECLARE @tzKey varchar(1000);
DECLARE @offset varchar(1000);
DECLARE @tzi binary(56);
DECLARE @substr varchar(1000);
DECLARE @pos int;
DECLARE @hours int;
DECLARE @minutes int;
DECLARE @start int;
DECLARE @end int;
DECLARE @isNegative bit;
DECLARE @utcOffset INT = DATEDIFF(minute, GETDATE(), GETUTCDATE());
DECLARE @parsedOffset INT;
DECLARE @midnight datetime2 = CAST(DATEFROMPARTS(DATEPART(year, GETDATE()), DATEPART(month, GETDATE()), DATEPART(day, GETDATE())) AS datetime2);
DECLARE @testdate datetime2 = CAST(DATEFROMPARTS(DATEPART(year, GETDATE()), DATEPART(month, GETDATE()), DATEPART(day, GETDATE())) AS datetime2);
 
DECLARE @timeZoneNames TABLE
(
  SubKeyName varchar(1000)
)
 
DECLARE @timeZones TABLE
(
  Name varchar(1000),
  Offset varchar(1000),
  ParsedOffset int,
  BiasMinutes int,
  ExtraBias_Std int,
  ExtraBias_DST int
)
 
INSERT INTO @timeZoneNames EXEC master..xp_regenumkeys @root, @key
 
DECLARE tz_cursor CURSOR FOR 
SELECT SubKeyName FROM @timeZoneNames
 
OPEN tz_cursor  
FETCH NEXT FROM tz_cursor INTO @tzName  
 
WHILE @@FETCH_STATUS = 0  
BEGIN
       SET @tzKey = @key + '\' + @tzName
       exec master.dbo.xp_regread @root, @tzKey, 'Display', @offset OUTPUT
       SET @pos  = CHARINDEX(':', @offset);
 
       IF @pos > 0
       BEGIN
              SET @substr = SUBSTRING(@offset, 1, @pos);
              SET @isNegative = CASE WHEN PATINDEX('%-%', @substr) > 0 THEN 1 ELSE 0 END;
              SET @start = CASE WHEN @isNegative = 1 THEN PATINDEX('%-%', @substr) ELSE PATINDEX('%+%', @substr) END + 1;
              SET @end = PATINDEX('%[^0-9]%', SUBSTRING(@substr, @start, LEN(@substr)))-1
              SET @hours = CAST(SUBSTRING(@substr, @start, @end) AS int) * CASE WHEN @isNegative = 1 THEN -1 ELSE 1 END;
 
              SET @substr = SUBSTRING(@offset, @pos, LEN(@offset));
              SET @start = PATINDEX('%[0-9]%', @substr);
              SET @end = PATINDEX('%[^0-9]%',SUBSTRING(@substr, @start, LEN(@substr)))-1
              SET @minutes = SUBSTRING(@substr, @start, @end);
 
              exec master.dbo.xp_regread @root, @tzKey, 'TZI', @tzi OUTPUT
              INSERT INTO @timeZones
              SELECT
              @tzName,
              @offset,
              @hours * 60 + @minutes,
              -- See http://msdn.microsoft.com/ms725481
              CAST(CAST(REVERSE(SUBSTRING(@tzi,  1, 4)) AS binary(4))      AS int) AS BiasMinutes,   -- UTC = local + bias: > 0 in US, < 0 in Europe!
              CAST(CAST(REVERSE(SUBSTRING(@tzi,  5, 4)) AS binary(4))      AS int) AS ExtraBias_Std, --   0 for most timezones
              CAST(CAST(REVERSE(SUBSTRING(@tzi,  9, 4)) AS binary(4))      AS int) AS ExtraBias_DST;
       END
       FETCH NEXT FROM tz_cursor INTO @tzName 
END  
 
CLOSE tz_cursor  
DEALLOCATE tz_cursor
 
-- Let's say the dealer makes a sale at 8PM PST
SET @testdate = DATEADD(hour, 18, CAST(DATEFROMPARTS(DATEPART(year, GETDATE()), DATEPART(month, GETDATE()), DATEPART(day, GETDATE())) AS datetime2));
 
-- What does that look like in UTC time? What does our configured date at midnight look like?  What does midnight in Knoxville look like in UTC?
-- This shows that we have to have a point of reference to compare the configured dates.  They either need a fixed offset for the region or by sale.
SELECT TOP 1 @parsedOffset = ParsedOffset FROM @timeZones WHERE Name LIKE 'Pacific Standard Time';
SELECT DATEADD(minute, @parsedOffset, @testdate) AS ConvertPSTToUTC, DATEADD(minute, @parsedOffset, DATEADD(day, 1, @midnight)) AS MidnightUTC, DATEADD(minute, @utcOffset, @midnight) AS LocalMidnightUTC
 
SELECT Name, ParsedOffset, DATEADD(minute, @utcOffset, @midnight) AS LocalMidnight, DATEADD(minute, ParsedOffset, @midnight) AS TimeZoneMidnight FROM @timeZones

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.