By: Aaron Bertrand | Updated: 2014-03-10 | Comments (3) | Related: 1 | 2 | 3 | > Dates
Problem
In my last tip on time zone conversions, I demonstrated how to use a small calendar table, in conjunction with two user-defined functions, to accurately convert datetime values between UTC and Eastern Time, taking Daylight Saving Time into account. Of course in most environments you need more flexibility; specifically, the ability to switch between any two time zones.
Solution
In this tip, I'm going to expand upon the calendar table I described earlier, adding support for multiple time zones. I'm actually going to create two tables: a time zone table, with mostly metadata about the time zone across all calendar years, and a calendar table with a specific row for each year in every supported time zone.
In the interests of simplicity and brevity, I'm going to include the major American time zones (Eastern, Central, Mountain, and Pacific), as well as UTC and GMT/British Summer Time. To balance this simplification, I've also added two time zones that have half-hour offsets (Newfoundland and Central Australia (Darwin)); the latter is on the other side of the date line (so ahead of UTC, unlike North America) and does not observe Daylight Saving Time at all. This will provide a well-rounded sample of the types of time zones you may need to support, without drowning you in an exhaustive sea of every single time zone on earth.
CREATE TABLE dbo.TimeZones
(
TimeZoneID TINYINT NOT NULL PRIMARY KEY,
StandardDescription VARCHAR(64) NOT NULL UNIQUE,
DaylightDescription VARCHAR(64) NOT NULL UNIQUE,
StandardOffsetMinutes SMALLINT NOT NULL,
DaylightOffsetMinutes SMALLINT NOT NULL
);
INSERT dbo.TimeZones VALUES
(1,'UTC','UTC',0,0),
(2,'Eastern Standard Time','Eastern Daylight Time', -300,-240),
(3,'Central Standard Time','Central Daylight Time', -360,-300),
(4,'Mountain Standard Time','Mountain Daylight Time', -420,-360),
(5,'Pacific Standard Time','Pacific Daylight Time', -480,-420),
(6,'Newfoundland Standard Time','Newfoundland Daylight Time', -210,-150),
(7,'Greenwich Mean Time','British Summer Time', -60,0),
(8,'Australia Central Standard Time','Australia Central Standard Time',570,570);
CREATE TABLE dbo.TZCalendar
(
[Year] DATE NOT NULL,
TimeZoneID TINYINT NOT NULL FOREIGN KEY
REFERENCES dbo.TimeZones(TimeZoneID),
UTC_DST_Start SMALLDATETIME NOT NULL,
UTC_DST_End SMALLDATETIME NOT NULL,
Local_DST_Start SMALLDATETIME NOT NULL,
Local_DST_End SMALLDATETIME NOT NULL
PRIMARY KEY ([Year], TimeZoneID)
);As with the previous tip, the most complicated part of this scenario is populating the calendar table. I won't go into how many iterations this took to get right; the iterations could make for a tip or blog post all on their own, though a lot of the logic that drove this query was explained in the first tip in this series. For the North American time zones, the calculations for when DST starts mimic those described earlier; for time zones that don't shift for DST (including UTC, obviously), we just pretend that DST lasts all year, but the "DST offset" is actually the same as the standard offset.
SET DATEFIRST 7;
;WITH cte(d,p) AS
(
-- all the years from 2000 through 50 years after the current year:
SELECT TOP (YEAR(GETDATE())-2000+51) DATEADD(YEAR,number,'20000101'),
p = CASE WHEN number < 7 THEN 1 ELSE 0 END -- year < 2007 = 1, else 0
FROM [master].dbo.spt_values WHERE [type] = N'P' ORDER BY number
),
tz(tz,som,dom,ch,cm,cd1,cd2,locs,loce) AS
(
SELECT TimeZoneID, StandardOffsetMinutes, DaylightOffsetMinutes,
CASE TimeZoneID WHEN 7 THEN 0 ELSE 2 END, -- 1:00 AM or 2:00 AM
CASE TimeZoneID WHEN 7 THEN 3 ELSE 2 END, -- April or March
CASE TimeZoneID WHEN 7 THEN -7 ELSE 7 END, -- Week earlier or second week
CASE TimeZoneID WHEN 7 THEN -7 ELSE 0 END, -- Week earlier or first week
CASE TimeZoneID WHEN 7 THEN 120 ELSE 60 END,
CASE TimeZoneID WHEN 7 THEN 60 ELSE -60 END
FROM dbo.TimeZones
),
cal(d,tz,som,dom,utcstart,utcend,locs,loce) AS
(
SELECT cte.d,tz.tz,tz.som,tz.dom,
-- UTC start of DST (start of year for areas that don't observe)
CASE WHEN tz.tz IN (1,8) THEN cte.d
ELSE
-- US/Canada:
-- First Sunday in April (< 2007)
-- Second Sunday in March (since 2007)
-- England: Last Sunday in March (since 1995)
DATEADD(MINUTE,-tz.som,DATEADD(HOUR,tz.ch,
DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,tz.cm
-CASE WHEN tz.tz <> 7 THEN p ELSE 0 END,cte.d))+1)%7+tz.cd1
-CASE WHEN tz.tz <> 7 THEN 7*p ELSE 0 END,DATEADD(MONTH,tz.cm
+CASE WHEN tz.tz <> 7 THEN p ELSE 0 END,cte.d))))
END,
-- UTC end of DST (start of *following* year for areas that don't observe)
CASE WHEN tz.tz IN (1,8) THEN DATEADD(YEAR, 1, cte.d)
ELSE
-- US/Canada:
-- Last Sunday in October (< 2007) or
-- First Sunday in November (since 2007)
-- England: Last Sunday in October:
DATEADD(MINUTE,-tz.dom,DATEADD(HOUR,tz.ch,
DATEADD(DAY,(7-DATEPART(WEEKDAY,DATEADD(MONTH,10,cte.d))
+1)%7+tz.cd2-CASE WHEN tz.tz <> 7 THEN (7*p) ELSE 0 END,
DATEADD(MONTH,10,cte.d))))
END,locs,loce
FROM cte CROSS JOIN tz
)
INSERT dbo.TZCalendar
SELECT d,tz,utcstart,utcend,
CASE WHEN tz IN (1,8) THEN utcstart ELSE DATEADD(MINUTE,som+locs,utcstart) END,
CASE WHEN tz IN (1,8) THEN utcend ELSE DATEADD(MINUTE,dom+loce,utcend) END
FROM cal ORDER BY d;
This creates a table with 520 rows (65 years across 8 time zones); it weighs in at only 40 KB. Again, as with our smaller calendar table from the previous tip, this table should be in memory all of the time, as long as it is being referenced often enough. The data looks like this:
Year TimeZoneID UTC_DST_Start UTC_DST_End Local_DST_Start Local_DST_End ---------- ---------- ---------------- ---------------- ---------------- ---------------- 2000-01-01 1 2000-01-01 00:00 2001-01-01 00:00 2000-01-01 00:00 2001-01-01 00:00 2000-01-01 2 2000-04-06 07:00 2000-10-29 06:00 2000-04-06 03:00 2000-10-29 01:00 2000-01-01 3 2000-04-06 08:00 2000-10-29 07:00 2000-04-06 03:00 2000-10-29 01:00 2000-01-01 4 2000-04-06 09:00 2000-10-29 08:00 2000-04-06 03:00 2000-10-29 01:00 2000-01-01 5 2000-04-06 10:00 2000-10-29 09:00 2000-04-06 03:00 2000-10-29 01:00 2000-01-01 6 2000-04-06 05:30 2000-10-29 04:30 2000-04-06 03:00 2000-10-29 01:00 2000-01-01 7 2000-03-26 01:00 2000-10-29 00:00 2000-03-26 02:00 2000-10-29 01:00 2000-01-01 8 2000-01-01 00:00 2001-01-01 00:00 2000-01-01 00:00 2001-01-01 00:00 ... 2014-01-01 1 2014-01-01 00:00 2015-01-01 00:00 2014-01-01 00:00 2015-01-01 00:00 2014-01-01 2 2014-03-09 07:00 2014-11-02 06:00 2014-03-09 03:00 2014-11-02 01:00 2014-01-01 3 2014-03-09 08:00 2014-11-02 07:00 2014-03-09 03:00 2014-11-02 01:00 2014-01-01 4 2014-03-09 09:00 2014-11-02 08:00 2014-03-09 03:00 2014-11-02 01:00 2014-01-01 5 2014-03-09 10:00 2014-11-02 09:00 2014-03-09 03:00 2014-11-02 01:00 2014-01-01 6 2014-03-09 05:30 2014-11-02 04:30 2014-03-09 03:00 2014-11-02 01:00 2014-01-01 7 2014-03-30 01:00 2014-10-26 00:00 2014-03-30 02:00 2014-10-26 01:00 2014-01-01 8 2014-01-01 00:00 2015-01-01 00:00 2014-01-01 00:00 2015-01-01 00:00 ... 2064-01-01 1 2064-01-01 00:00 2065-01-01 00:00 2064-01-01 00:00 2065-01-01 00:00 2064-01-01 2 2064-03-09 07:00 2064-11-02 06:00 2064-03-09 03:00 2064-11-02 01:00 2064-01-01 3 2064-03-09 08:00 2064-11-02 07:00 2064-03-09 03:00 2064-11-02 01:00 2064-01-01 4 2064-03-09 09:00 2064-11-02 08:00 2064-03-09 03:00 2064-11-02 01:00 2064-01-01 5 2064-03-09 10:00 2064-11-02 09:00 2064-03-09 03:00 2064-11-02 01:00 2064-01-01 6 2064-03-09 05:30 2064-11-02 04:30 2064-03-09 03:00 2064-11-02 01:00 2064-01-01 7 2064-03-30 01:00 2064-10-26 00:00 2064-03-30 02:00 2064-10-26 01:00 2064-01-01 8 2064-01-01 00:00 2065-01-01 00:00 2064-01-01 00:00 2065-01-01 00:00
This time, we'll create a single function that takes in a date/time in any of the supported time zones, and converts it to any of the other supported time zones. The simplest way to think about this is to convert the source time to UTC, then convert to the target time zone. So that's what the function will do (and it does still take into account edge cases, for example it returns NULL if you pass in 2014-03-09 02:30 and say that was in the Eastern time zone):
CREATE FUNCTION dbo.ConvertBetweenTimeZones
(
@Origin DATETIME,
@SourceTZ TINYINT,
@TargetTZ TINYINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT
Origin = @Origin,
src.OriginTimeZone,
[Target] = DATEADD(MINUTE, CASE
WHEN src.UTC >= trg.UTC_DST_Start
AND src.UTC < trg.UTC_DST_End THEN tz.DaylightOffsetMinutes
ELSE tz.StandardOffsetMinutes END, src.UTC),
TargetTimeZone = CASE
WHEN src.UTC >= trg.UTC_DST_Start
AND src.UTC < trg.UTC_DST_End THEN tz.DaylightDescription
ELSE tz.StandardDescription END
FROM
(
SELECT
src.[Year],
UTC = DATEADD(MINUTE, -CASE
WHEN @Origin >= src.Local_DST_Start
AND @Origin < src.Local_DST_End THEN t.DaylightOffsetMinutes
WHEN @Origin >= DATEADD(HOUR,-1,src.Local_DST_Start)
AND @Origin < src.Local_DST_Start THEN NULL
ELSE t.StandardOffsetMinutes END, @Origin),
OriginTimeZone = CASE
WHEN @Origin >= src.Local_DST_Start
AND @Origin < src.Local_DST_End THEN t.DaylightDescription
ELSE t.StandardDescription END
FROM dbo.TZCalendar AS src
INNER JOIN dbo.TimeZones AS t
ON src.TimeZoneID = t.TimeZoneID
WHERE src.TimeZoneID = @SourceTZ
AND t.TimeZoneID = @SourceTZ
AND CONVERT(DATE,DATEADD(MINUTE,t.StandardOffsetMinutes,@Origin))
>= src.[Year]
AND CONVERT(DATE,DATEADD(MINUTE,t.StandardOffsetMinutes,@Origin))
< DATEADD(YEAR, 1, src.[Year])
) AS src
INNER JOIN dbo.TZCalendar AS trg
ON CONVERT(DATE,src.UTC) >= trg.[Year]
AND CONVERT(DATE,src.UTC) < DATEADD(YEAR, 1, trg.[Year])
INNER JOIN dbo.TimeZones AS tz
ON trg.TimeZoneID = tz.TimeZoneID
WHERE trg.TimeZoneID = @TargetTZ
AND tz.TimeZoneID = @TargetTZ
);And we can use the function as follows (again, I've chosen values that hit our edge cases):
DECLARE @d TABLE ( ID INT, Origin DATETIME, SourceTimeZone TINYINT, TargetTimeZone TINYINT ); INSERT @d VALUES (1, '2014-03-09 03:00', 2, 1),(2, '2014-03-09 03:00', 2, 8), (3, '2014-03-09 03:00', 8, 1),(4, '2014-03-09 03:00', 8, 2), (5, '2014-03-09 02:30', 2, 1),(6, '2014-11-02 05:30', 1, 2), (7, '2014-11-02 06:30', 8, 2),(8, '2014-11-02 02:30', 8, 1), (9, '2014-11-02 05:30', 8, 2),(10,'2014-11-02 06:30', 7, 2); SELECT d.Origin, f.OriginTimeZone, f.[Target], f.TargetTimeZone FROM @d AS d OUTER APPLY dbo.ConvertBetweenTimeZones (d.Origin, d.SourceTimeZone, d.TargetTimeZone) AS f;
Results:
Feel free to try the function with any other input date/time values and time zones (at least of those that I've populated for you). You can compare the results with online services like Helloka's World Time Buddy.
You may wish to include other information in the output, for example the abbreviation of the destination time zone (which you could easily add to the TimeZones table), or a flag indicating that a Daylight Saving Time adjustment took place. You could also probably come up with ways to short circuit the function (for example, if the source and target time zone are the same) or to make the logic smarter, such that you don't have to switch to UTC first. In my next installment, I'll talk about enhancing this function to take into account the preferred time zone of users.
Next Steps
- Consider using a calendar table to vastly simplify your time zone conversions in SQL Server.
- Read the following tips and other resources:
- Handle conversion between time zones in SQL Server - part 1
- Converting UTC to local time with SQL Server CLR
- Convert SQL Server DateTime Data Type to DateTimeOffset Data Type
- Daylight Savings Time Functions in SQL Server
- Daylight saving time and time zone best practices (Stack Overflow)
- DATETIMEOFFSET and SWITCHOFFSET (MSDN)
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-03-10
