Generate DateTime based on Time Offset

So let’s say you have a table with a column of type DateTime. Now you have to support timezones. The first thing you create is a table of time offsets (Google Time Offsets). The table would look like this:

Here’s schema for it. You can download the full script with the data as well.

CREATE TABLE [dbo].[TimeZones](
  [TimeZoneID] [int] IDENTITY(1,1) NOT NULL,
  [Offset] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [TimeLabel] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_TimeZones] PRIMARY KEY CLUSTERED 
(
  [TimeZoneID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Now for the purpose of this tutorial, let’s create an event table. This will have a set of dates/times which we’ll use to add the timeoffsets to. The table looks like this:

and here’s the script:

CREATE TABLE [dbo].[TimeEvent](
  [TimeEventID] [int] IDENTITY(1,1) NOT NULL,
  [EventName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [EventDateTime] [datetime] NULL,
  [TimeZoneID] [int] NULL,
 CONSTRAINT [PK_TimeEvent] PRIMARY KEY CLUSTERED 
(
  [TimeEventID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Now let’s INNER JOIN the tables properly adding the timeoffset to the event datetime:

SELECT  tz.TimeZoneID,
        tz.Offset,        
        te.TimeEventID,
        te.EventName,
        te.EventDateTime AS 'OriginalEventDateTime',

        -- Hour - Extract Hour from Offset
        SUBSTRING(Offset, 0, CHARINDEX(':', Offset, 0)) AS 'Hour',
        
        -- Minute - Extract Minute from Offset        
        SUBSTRING(Offset, CHARINDEX(':', Offset, 0) + 1, LEN(Offset)) AS 'Minute',
        
        -- New Date with UTC Addition
        DATEADD(HOUR,
                CAST(SUBSTRING(Offset, 0, CHARINDEX(':', Offset, 0)) AS INT),
                DATEADD(MINUTE,
                        CAST(SUBSTRING(Offset, CHARINDEX(':', Offset, 0) + 1,
                                       LEN(Offset)) AS INT), EventDateTime -- DateTime change via Offset
                         )) AS 'EventDateTimeWithTimeZone'
FROM    TimeZones tz
        INNER JOIN TimeEvent te ON tz.TimeZoneID = te.TimeZoneID

Here’s the result: