Handle conversion between time zones in SQL Server

Problem

In my first tip on time zone conversions, I demonstrated how to use a small calendar table and a user-defined function to easily translate date/time values between two time zones, incorporating offsets due to Daylight Saving Time. Another thing you may want to support is the ability to convert to a user's preferred time zone without requiring them to specify it every time.

Solution

In this tip, I'm going to expand upon the previous examples by adding a Users table, where each user has a pre-defined preferred time zone. I'll also show how to use the user-defined function to show all attendees of a meeting when the meeting will occur in their own time zone.

So first, we'll create a Users table, and populate it with some users. Remember from the previous tip that we have a set of 8 time zones we currently support:

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.Users
(
  UserID INT NOT NULL PRIMARY KEY,
  Name NVARCHAR(32) NOT NULL,
  TimeZoneID TINYINT NOT NULL FOREIGN KEY
    REFERENCES dbo.TimeZones(TimeZoneID)
  /* , other columns, surely */
);
INSERT dbo.Users(UserID,Name,TimeZoneID) VALUES
(1,N'Bob',2),   -- New York office
(2,N'Frank',5), -- San Francisco office
(3,N'Nigel',7); -- London office

Now we can change our previous function to also take the UserID, and only require either the source or the destination TimeZoneID. This way, the user can specify a local time to convert to a destination time zone, or a time in a different time zone to convert to their own, and in both cases they wouldn't have to specify their own TimeZoneID. For example:

CREATE FUNCTION dbo.ConvertBetweenTimeZones2
(
  @Origin   DATETIME,
  @SourceTZ TINYINT = NULL,
  @TargetTZ TINYINT = NULL,
  @UserID   INT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
 RETURN 
 (
  WITH z AS 
  (
    SELECT 
      SourceTZ = COALESCE(@SourceTZ, TimeZoneID),
      TargetTZ = COALESCE(@TargetTZ, TimeZoneID)
    FROM dbo.Users WHERE UserID = @UserID
  )
  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
    INNER JOIN z ON src.TimeZoneID = z.SourceTZ 
      AND t.TimeZoneID = z.SourceTZ
    WHERE 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
  INNER JOIN z ON trg.TimeZoneID = z.TargetTZ
  AND tz.TimeZoneID = z.TargetTZ
);

Just for a quick sample usage, we can see how we would convert a date/time value in UTC to each of our three users' time zones:

SELECT u.UserID, f.Origin, f.OriginTimeZone,
    f.[Target], f.TargetTimeZone
  FROM dbo.Users AS u
  OUTER APPLY dbo.ConvertBetweenTimeZones2
  ('20140309 03:45', 1, u.TimeZoneID, u.UserID) AS f;

Results:

UserID  Origin            OriginTimeZone  Target            TargetTimeZone
------  ----------------  --------------  ----------------  ---------------------
1       2014-03-09 03:45  UTC             2014-03-08 22:45  Eastern Standard Time
2       2014-03-09 03:45  UTC             2014-03-08 19:45  Pacific Standard Time
3       2014-03-09 03:45  UTC             2014-03-09 02:45  Greenwich Mean Time

It's useful to note, though, that pulling the TimeZoneID from the Users table is, by design, not required. This gives the same results, since the TimeZoneID is pulled in by the function:

SELECT u.UserID, f.Origin, f.OriginTimeZone,
    f.[Target], f.TargetTimeZone
  FROM dbo.Users AS u
  OUTER APPLY dbo.ConvertBetweenTimeZones2
  ('20140309 03:45', 1, NULL, u.UserID) AS f;
------------------------^^^^

Not very helpful in this sample usage, but let's consider a more elaborate example; let's say we have a Meetings table, which stores information about a meeting (including the scheduled time, in UTC of course), and a MeetingUsers table, which indicates all of the users who will be participating in the meeting.

CREATE TABLE dbo.Meetings
(
  MeetingID INT PRIMARY KEY,
  Description NVARCHAR(255),
  ScheduledTime DATETIME NOT NULL,
  TimeZoneID TINYINT NOT NULL DEFAULT (1)
    FOREIGN KEY REFERENCES dbo.TimeZones(TimeZoneID)
  /* other columns too, surely */
);
INSERT dbo.Meetings VALUES
(1,N'Discussion on HR Policies','20140208 11:00',1),
(2,N'Review of new HR Policies','20140415 11:30',1);
CREATE TABLE dbo.MeetingUsers
(
  MeetingID INT NOT NULL FOREIGN KEY 
    REFERENCES dbo.Meetings(MeetingID),
  UserID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.Users(UserID),
  PRIMARY KEY (MeetingID,UserID) 
);
INSERT dbo.MeetingUsers VALUES
(1,1),(1,3),(2,1),(2,2),(2,3);

So now, we want to derive the meetings, which users are involved, and what time zone each user should expect to attend the meeting. We can do this with this simple query:

SELECT m.Description, mu.UserID, f.[Target], f.TargetTimeZone
FROM dbo.Meetings AS m
INNER JOIN dbo.MeetingUsers AS mu
ON m.MeetingID = mu.MeetingID
OUTER APPLY dbo.ConvertBetweenTimeZones2
(m.ScheduledTime,m.TimeZoneID,NULL,mu.UserID) AS f;

Results:

Description                UserID  Target            TargetTimeZone
-------------------------  ------  ----------------  ---------------------
Discussion on HR Policies  1       2014-02-08 06:00  Eastern Standard Time
Discussion on HR Policies  3       2014-02-08 10:00  Greenwich Mean Time
Review of new HR Policies  1       2014-04-15 07:30  Eastern Daylight Time
Review of new HR Policies  2       2014-04-15 04:30  Pacific Daylight Time
Review of new HR Policies  3       2014-04-15 11:30  British Summer Time

Since the function knows how to get the TimeZoneID of the user, we don't have to add joins to pull the source or target time zone from the Users table (and while you could argue that you'd also need to pull the user name, that could potentially be added to the output of the above query and be satisfied by a skinnier index than one that includes the TimeZoneID column).

For an application that performs a lot of queries that convert date/time values between time zones based on user preferences, this simplification can pay off across a lot of code. And in cases where the source or target time zone is known and shouldn't depend on any specific user, you can still choose to use the previous version of the function.

Next Steps

Consider using a calendar table to vastly simplify your time zone conversions in SQL Server.
Read the following tips and other resources:

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-04-01

Handle conversion between time zones in SQL Server - part 3

Problem

Solution

Next Steps

About the author

Comments For This Article