Timezone conversion using pre-defined offset values

Question

0.00/5 (No votes)

See more:

Hi
I was searching through the submitted articles and found a reference for a timezone conversion in SQL Server: Convert GMT to Local Date time.

I am attempting to run reports on events. I must convert a UTC event time to local time based on the timezone value provided in the row data. The article above is excellent. However, my dilemma is that the data I have does not match, in the sense that my data structure is different.

Here is an example of my timezone data:

1 (GMT+04:30) Kabul 270 0 0 0 0 0 0 0 -270 0 -60 0 0 0 0 0 0 Afghanistan Standard Time

The timezone table can be created with the following script:

SQL

USE [Demo_Model]
GO
/****** Object:  Table [dbo].[WORLDTZ]    Script Date: 05/12/2010 11:49:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WORLDTZ](
    [WORLDTZID] [int] NOT NULL,
    [DISPLAYNAME] [nvarchar](128) NOT NULL,
    [GMTOFFSET] [int] NOT NULL,
    [DST] [smallint] NOT NULL,
    [STANDARDMONTH] [int] NOT NULL,
    [STANDARDDAY] [int] NOT NULL,
    [STANDARDWEEK] [int] NOT NULL,
    [DAYLIGHTMONTH] [int] NOT NULL,
    [DAYLIGHTDAY] [int] NOT NULL,
    [DAYLIGHTWEEK] [int] NOT NULL,
    [BIAS] [int] NOT NULL,
    [STANDARDBIAS] [int] NOT NULL,
    [DAYLIGHTBIAS] [int] NOT NULL,
    [STANDARDHOUR] [int] NOT NULL,
    [DAYLIGHTHOUR] [int] NOT NULL,
    [STANDARDMINUTE] [smallint] NOT NULL DEFAULT ((0)),
    [STANDARDSECOND] [smallint] NOT NULL DEFAULT ((0)),
    [DAYLIGHTMINUTE] [smallint] NOT NULL DEFAULT ((0)),
    [DAYLIGHTSECOND] [smallint] NOT NULL DEFAULT ((0)),
    [WINDOWS_TZID] [nvarchar](32) NULL,
 CONSTRAINT [WORLDTZ_PK] PRIMARY KEY NONCLUSTERED
(
    [WORLDTZID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

I am struggling with SQL UTC functions to create a method to read in my UTC value and WorldTZID to generate the local time value.

Posted 11-May-10 23:59pm

vasiles1

Updated 12-May-10 0:21am

Smitha Nishant

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Rod Kemp · Answer 1 · 2010-05-12T21:24:00

First up your database table doesn't make dealing with daylight savings times easy as such you will need multiple steps to to convert UTC/GMT to local time.
Start with creating some variables to hold GMTOFFSET, DAYLIGHTBIAS, STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND
You then need to select the data from the WORLDTZ table into those variables like;

SELECT @GMTOFFEST=GMTOFFSET, @DAYLIGHTBIAS=DAYLIGHTBIAS, ...
FROM WORLDTZ
WHERE WORLDTZID= @WORLDTZID

Next get the local datetime;

SET @LOCALDATETIME = DATEADD(n, @GMTOFFEST, @EVENTDATETIME)

You now have a datetime value that does not take into account daylight savings.
You now need to adjust the @LOCALDATETIME variable for daylight savings if it is applicable.
Look at the STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND variables if they are all the same you don't need to adjust the @LOCALDATETIME variable if that are different you need to determine if your event datetime value falls within the daylight savings time for the timezone.

To determine the daylight savings start you need the year from the @EVENTDATETIME variable and then combine that with the DAYLIGHTMONTH/DAY/HOUR/MINUTE/SECOND vairables to give a start datetime for daylight savings i.e. @DAYLIGHTSTART now you need to determine if daylight savings starts and ends in the same year.

For northern hemisphere daylight savings

if STANDARDMONTH > DAYLIGHTMONTH then
 Combine STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and year from <code>@EVENTDATETIME</code> to give <code>@DAYLIGHTEND</code>
    if @LOCALDATETIME >= @DAYLIGHTSTART AND @LOCALDATETIME <= @DAYLIGHTEND then
        SET @LOCALDATETIME = DATEADD(n, @DAYLIGHTBIAS, @LOCALDATETIME)

For southern hemisphere daylight savings

if STANDARDMONTH < DAYLIGHTMONTH then
Combine STANDARDMONTH/DAY/HOUR/MINUTE/SECOND and year from @EVENTDATETIME to give @DAYLIGHTEND
Add 1 year to @DAYLIGHTEND to get @DAYLIGHTENDNEXTYEAR

if @LOCALDATETIME < @DAYLIGHTEND OR (@LOCALDATETIME >= @DAYLIGHTSTART AND @LOCALDATETIME <= @DAYLIGHTENDNEXTYEAR) then
    SET @LOCALDATETIME = DATEADD(n, @DAYLIGHTBIAS, @LOCALDATETIME)

Or you could change the WORLDTZ table to reflect something closer to the article you linked to as one other problem your current table has is that it doesn't take into account countries that may change the date they enter end leave daylight savings which does occur for various reasons.