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:
Categories