Skip to content

Instantly share code, notes, and snippets.

@drumsta
Last active January 18, 2023 13:50
Show Gist options
  • Save drumsta/16b79cee6bc195cd89c8 to your computer and use it in GitHub Desktop.
Save drumsta/16b79cee6bc195cd89c8 to your computer and use it in GitHub Desktop.
Sample MS SQL gist to convert Local time to UTC time.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.TimeZoneInfo') AND type IN (N'U'))
DROP TABLE dbo.TimeZoneInfo
CREATE TABLE dbo.TimeZoneInfo (
TimeZoneID int IDENTITY(1, 1) NOT NULL,
Display nvarchar(255) NOT NULL,
Bias smallint NOT NULL,
StdBias smallint NOT NULL,
DltBias smallint NOT NULL,
StdMonth smallint NOT NULL,
StdDayOfWeek smallint NOT NULL,
StdWeek smallint NOT NULL,
StdHour smallint NOT NULL,
DltMonth smallint NOT NULL,
DltDayOfWeek smallint NOT NULL,
DltWeek smallint NOT NULL,
DltHour smallint NOT NULL,
CONSTRAINT PK_TimeZoneInfo PRIMARY KEY CLUSTERED (TimeZoneID ASC)
)
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetDaylightStandardDateTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.GetDaylightStandardDateTime
GO
CREATE FUNCTION GetDaylightStandardDateTime
(
@Year int, -- a valid year value
@Month int, -- 1..12
@DayOfWeek smallint, -- 1..7
@Week smallint, -- 1..5, 1 - first week, 2 - second, etc., 5 - the last week
@Hour smallint -- hour value when daylight or standard time begins.
)
RETURNS datetime
AS
BEGIN
DECLARE @FirstOfMonth datetime;
DECLARE @DoW smallint;
DECLARE @Ret datetime;
-- find day of the week of the first day of a given month:
SET @FirstOfMonth = CAST(@Year AS NVARCHAR) + '/' + CAST(@Month AS NVARCHAR) + '/01';
-- 5th week means the last week of the month, so go one month forth, then one week back
IF @Week = 5
SET @FirstOfMonth = DATEADD(Month, 1, @FirstOfMonth);
SET @DoW = DATEPART(weekday, @FirstOfMonth);
-- find first given day of the week of the given month:
IF @DoW > @DayOfWeek
SET @Ret = DATEADD(Day, 7 + @DayOfWeek - @DoW, @FirstOfMonth)
ELSE
SET @Ret = DATEADD(Day, @DayOfWeek - @DoW, @FirstOfMonth);
-- advance to the given week (5th week means the last one of the month)
IF @Week < 5
SET @Ret = DATEADD(Week, @Week - 1, @Ret)
ELSE
-- the last week of the previous month; go one week backward
SET @Ret = DATEADD(Week, -1, @Ret);
SET @Ret = DATEADD(Hour, @Hour, @Ret);
RETURN @Ret
END
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.GetLocalDateTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION dbo.GetLocalDateTime
GO
CREATE FUNCTION dbo.GetLocalDateTime
(
@UTCDate datetime,
@TimeZoneID smallint
)
RETURNS datetime
AS
BEGIN
DECLARE @LocalDateTime datetime
DECLARE @DltBiasFactor smallint
DECLARE @Display nvarchar(50)
DECLARE @Bias int
DECLARE @DltBias int
DECLARE @StdMonth smallint
DECLARE @StdDow smallint
DECLARE @StdWeek smallint
DECLARE @StdHour smallint
DECLARE @DltMonth smallint
DECLARE @DltDow smallint
DECLARE @DltWeek smallint
DECLARE @DltHour smallint
DECLARE @DaylightDate datetime
DECLARE @StandardDate datetime
SET @DltBiasFactor = 0
SELECT @Display = Display,
@Bias = (-1 * Bias),
@DltBias = (-1 * DltBias),
@StdMonth = StdMonth,
@StdDow = StdDayOfWeek + 1,
@StdWeek = StdWeek,
@StdHour = StdHour,
@DltMonth = DltMonth,
@DltDow = DltDayOfWeek + 1,
@DltWeek = DltWeek,
@DltHour = DltHour
FROM dbo.TimeZoneInfo
WHERE TimeZoneID = @TimeZoneID
IF @StdMonth = 0
BEGIN
SET @LocalDateTime = DateAdd(minute, @Bias, @UTCDate)
END ELSE BEGIN
SET @StandardDate = dbo.GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @StdMonth, @StdDow, @StdWeek, @StdHour)
SET @DaylightDate = dbo.GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @DltMonth, @DltDow, @DltWeek, @DltHour)
IF ( @StandardDate > @DaylightDate)
BEGIN
IF (DATEADD(minute, @Bias, @UTCDate) BETWEEN @DaylightDate AND @StandardDate )
SET @DltBiasFactor = 1
END ELSE BEGIN
IF (DATEADD(minute, @Bias, @UTCDate) BETWEEN @StandardDate AND @DaylightDate)
SET @DltBiasFactor = 0
END
SET @LocalDateTime = DATEADD(minute, @Bias + (@DltBiasFactor * @DltBias), @UTCDate)
END
RETURN @LocalDateTime;
END
GO
TRUNCATE TABLE dbo.TimeZoneInfo
INSERT INTO dbo.TimeZoneInfo
([Display],[Bias],[StdBias],[DltBias],[StdMonth],[StdDayOfWeek],[StdWeek],[StdHour],[DltMonth],[DltDayOfWeek],[DltWeek],[DltHour])
VALUES
('(GMT-12:00) International Date Line West',720,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-11:00) Midway Island, Samoa',660,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-10:00) Hawaii',600,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-09:00) Alaska',540,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-08:00) Pacific Time (US & Canada); Tijuana',480,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-07:00) Chihuahua, La Paz, Mazatlan',420,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-07:00) Mountain Time (US & Canada)',420,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-07:00) Arizona',420,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-06:00) Guadalajara, Mexico City, Monterrey',360,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-06:00) Saskatchewan',360,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-06:00) Central America',360,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-06:00) Central Time (US & Canada)',360,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-05:00) Eastern Time (US & Canada)',300,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-05:00) Bogota, Lima, Quito',300,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-05:00) Indiana (East)',300,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-04:00) Caracas, La Paz',240,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-04:00) Santiago',240,0,-60,3,6,2,0,10,6,2,0)
, ('(GMT-04:00) Atlantic Time (Canada)',240,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-03:30) Newfoundland',210,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-03:00) Buenos Aires, Georgetown',180,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT-03:00) Brasilia',180,0,-60,2,0,2,2,10,0,3,2)
, ('(GMT-03:00) Greenland',180,0,-60,10,0,5,2,4,0,1,2)
, ('(GMT-02:00) Mid-Atlantic',120,0,-60,9,0,5,2,3,0,5,2)
, ('(GMT-01:00) Azores',60,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT-01:00) Cape Verde Is.',60,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT) Casablanca, Monrovia',0,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT) Greenwich Mean Time : Dublin, Edinburgh, Lisbon, London',0,0,-60,10,0,5,2,3,0,5,1)
, ('(GMT+01:00) Belgrade, Bratislava, Budapest, Ljubljana, Prague',-60,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+01:00) Sarajevo, Skopje, Warsaw, Zagreb',-60,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+01:00) Brussels, Copenhagen, Madrid, Paris',-60,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+01:00) West Central Africa',-60,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna',-60,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+02:00) Harare, Pretoria',-120,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+02:00) Jerusalem',-120,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+02:00) Athens, Beirut, Istanbul, Minsk',-120,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+02:00) Helsinki, Kyiv, Riga, Sofia, Tallinn, Vilnius',-120,0,-60,10,0,5,4,3,0,5,3)
, ('(GMT+02:00) Bucharest',-120,0,-60,10,0,5,1,3,0,5,0)
, ('(GMT+02:00) Cairo',-120,0,-60,9,3,5,2,5,5,1,2)
, ('(GMT+03:00) Nairobi',-180,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+03:00) Kuwait, Riyadh',-180,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+03:00) Baghdad',-180,0,-60,10,0,1,4,4,0,1,3)
, ('(GMT+03:00) Moscow, St. Petersburg, Volgograd',-180,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+03:30) Tehran',-210,0,-60,9,2,4,2,3,0,1,2)
, ('(GMT+04:00) Abu Dhabi, Muscat',-240,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+04:00) Baku, Tbilisi, Yerevan',-240,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+04:30) Kabul',-270,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+05:00) Ekaterinburg',-300,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+05:00) Islamabad, Karachi, Tashkent',-300,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+05:30) Chennai, Kolkata, Mumbai, New Delhi',-330,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+05:45) Kathmandu',-345,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+06:00) Almaty, Novosibirsk',-360,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+06:00) Sri Jayawardenepura',-360,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+06:00) Astana, Dhaka',-360,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+06:30) Rangoon',-390,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+07:00) Krasnoyarsk',-420,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+07:00) Bangkok, Hanoi, Jakarta',-420,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+08:00) Kuala Lumpur, Singapore',-480,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+08:00) Taipei',-480,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+08:00) Irkutsk, Ulaan Bataar',-480,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+08:00) Beijing, Chongqing, Hong Kong, Urumqi',-480,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+08:00) Perth',-480,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+09:00) Yakutsk',-540,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+09:00) Seoul',-540,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+09:00) Osaka, Sapporo, Tokyo',-540,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+09:30) Darwin',-570,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+09:30) Adelaide',-570,0,-60,3,0,5,3,10,0,5,2)
, ('(GMT+10:00) Canberra, Melbourne, Sydney',-600,0,-60,3,0,5,3,10,0,5,2)
, ('(GMT+10:00) Brisbane',-600,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+10:00) Guam, Port Moresby',-600,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+10:00) Hobart',-600,0,-60,3,0,5,3,10,0,1,2)
, ('(GMT+10:00) Vladivostok',-600,0,-60,10,0,5,3,3,0,5,2)
, ('(GMT+11:00) Magadan, Solomon Is., New Caledonia',-660,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+12:00) Fiji, Kamchatka, Marshall Is.',-720,0,-60,0,0,0,0,0,0,0,0)
, ('(GMT+12:00) Auckland, Wellington',-720,0,-60,3,0,3,2,10,0,1,2)
, ('(GMT+13:00) Nuku''alofa',-780,0,-60,0,0,0,0,0,0,0,0)
GO
/*
Example how to convert Local time to UTC time.
--*/
DECLARE @TimeZoneID smallint = 27; -- Dublin
SELECT dbo.GetLocalDateTime(GETUTCDATE(), @TimeZoneID);
SELECT dbo.GetLocalDateTime('2014-07-01', @TimeZoneID);
SELECT dbo.GetLocalDateTime('2014-01-01', @TimeZoneID);
@sbarclay8308
Copy link

Greetings,

Line 122 has an erroneous space between "dbo." and "GetDaylightStandardDateTime" that is not caught when you execute/create the stored procedure (BAD Microsoft) and runs without error but totally screws up the data (as I found out converting a huge DB to UTC)

Please remove this space so no-one else gets hosed by it

IF @StdMonth = 0
BEGIN
SET @LocalDateTime = DateAdd(minute, @bias, @UTCDate)
END ELSE BEGIN
SET @StandardDate = dbo.GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @StdMonth, @StdDow, @StdWeek, @StdHour)

BAD 122
SET @DaylightDate = dbo. GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @DltMonth, @DltDow, @DltWeek, @DltHour)

FIX 122
SET @DaylightDate = dbo.GetDaylightStandardDateTime(DATEPART(year, @UTCDate), @DltMonth, @DltDow, @DltWeek, @DltHour)

@drumsta
Copy link
Author

drumsta commented Jan 18, 2023

That is fixed, thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment