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, 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 |