Click here to Skip to main content
15,867,330 members
Articles / Database Development / SQL Server
Article

SQL 2005 Time Zone Conversion Functions

Rate me:
Please Sign up or sign in to vote.
4.89/5 (7 votes)
30 Nov 2008CPOL7 min read 173.5K   2.5K   49   51
How to create simple SQL time zone functions to use in queries.

Image 1

Introduction

I work for a rather large company that spans countries and time zones, with multiple data centers in multiple locations. While working on a project where data was coming from different locations into a common data repository, we ran into a situation. Our application developers had not setup the application to convert date-time values into a common time zone; each application used the local time of the servers that hosted the application. In engaging the vendor, it was soon discovered that it was cost prohibitive to re-write the application to do so. One of the design aspects of the application architecture included MS SQL Server 2005 as the back-end database software. I did a lot of research on Microsoft's website, and the only time zone conversion functions that I could find were relative to analysis services, and would not provide me with a solution to my problem. So, my options were to pay someone to modify our application, or create a process in the database environment to perform the conversions as data is inserted into the common database environment.

Using the Code

A few years ago, we were primarily an Oracle database shop. Oracle natively comes with some time zone conversion functions. My approach to resolve this issue was to setup an environment in MS SQL Server 2005 similar to the environment that I was used to in Oracle (a scalar function called NEW_TIME that took three parameters; date to convert, original time zone value, conversion time zone, and the function returns the converted value). The only thing about the Oracle date time conversion functions that I did not like was that you have to know whether you want to use the standard or the daylight code for the same time zone (e.g., CST vs. CDT). I think that it would be better for the functions to be able to determine whether the date should be converted using the daylight or standard offset.

First, I created a TIME_ZONES table to store the time zone conversion parameters (see the table structure below).

SQL
CREATE TABLE [dbo].[TIME_ZONES] (
    [TIMEZONE_CD] [varchar] (6) NOT NULL ,
    [TIMEZONE_NAME] [varchar] (60) NOT NULL ,
    [OFFSET_HR] [int] NOT NULL ,
    [OFFSET_MI] [int] NOT NULL ,
    [DST_OFFSET_HR] [int] NOT NULL ,
    [DST_OFFSET_MI] [int] NOT NULL ,
    [DST_EFF_DT] [varchar] (10) NOT NULL ,
    [DST_END_DT] [varchar] (10) NOT NULL ,
    [EFF_DT] DATETIME NOT NULL,
    [END_DT] DATETIME NOT NULL
)

The main challenge that I had in putting together this table was to determine how to come up with a way to use a generic code for the daylight start and end dates that would be applicable for any year, so that I did not have to maintain a table of actual dates and times that show when day light savings starts or ends for each time zone. So, I came up with a code that I could convert date time values to in order to determine if that date falls within day light or standard time. The code is formatted as follows; MMWDHHmm. MM = two digit month (e.g., March = 03), W = the week of the month (e.g., the second week of the month = 2), D = the day of the week, Sunday is the start of the week which starts at 1 (e.g., Monday = 2), HH = two digit hour, 24 hour time (e.g., 2:00 am = 02, and 2:00 pm = 14), mm = two digit minute (e.g., 35 minutes after the hour is 35). Example: On Sunday, on the second week of the month, for the month of March, at 2:00 am, would be: 03210200.

An example of one of the records is as follows: TIMEZONE_CD = 'CT', TIMEZONE_NAME = 'CENTRAL TIME', OFFSET_HR = -6, OFFSET_MI = 0, DST_OFFSET_HR = -5, DST_OFFSET_MI = 0, DST_EFF_DT = 03210200, DST_END_DT = '11110200' , EFF_DT = '11/30/2008', END_DT = '12/31/9999'.

After the table was created and populated, I started on the creation of the functions. I started with the function to convert a provided date-time to Universal (UTC) or Greenwich time (GMT). The function that I setup takes two parameters; the date-time to convert, and the time zone code. The function declares some variables, and then populates them with the values from the TIME_ZONES table using the provided time zone code. It then checks to see if the date-time provided was within the daylight effective and end dates, so that it would know which offset to adjust the provided date time with, and lastly, it returns the adjusted date-time in UTC time.

SQL
CREATE FUNCTION GET_UTCTIME 
    (@DT AS DATETIME, 
     @TZ AS VARCHAR(12))
RETURNS DATETIME
AS
BEGIN
-- DECLARE VARIABLES
    DECLARE @NEWDT AS DATETIME
    DECLARE @OFFSETHR AS INT
    DECLARE @OFFSETMI AS INT
    DECLARE @DSTOFFSETHR AS INT
    DECLARE @DSTOFFSETMI AS INT
    DECLARE @DSTDT AS VARCHAR(10)
    DECLARE @DSTEFFDT AS VARCHAR(10)
    DECLARE @DSTENDDT AS VARCHAR(10)
    
-- GET THE DST parameter from the provided datetime
    -- This gets the month of the datetime provided (2 char value)
    SELECT @DSTDT = CASE LEN(DATEPART(month, @DT)) 
                WHEN 1 
                    then '0' + CONVERT(VARCHAR(2),DATEPART(month, @DT)) 
                ELSE CONVERT(VARCHAR(2),DATEPART(month, @DT)) END
    -- This gets the occurrence of the day of the week within the month 
      -- (i.e. first Sunday, or second Sunday...) (1 char value)
    SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),(DATEPART(day,@DT) + 6) / 7)
    -- This gets the day of the week for the provided datetime (1 char value)
    SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),DATEPART(dw, @DT))
    -- This gets the hour for the provided datetime (2 char value)
    SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(hh, @DT)) 
                    WHEN 1 
                        then '0' + CONVERT(VARCHAR(2),DATEPART(hh, @DT)) 
                    ELSE CONVERT(VARCHAR(2),DATEPART(hh, @DT)) END
    -- This gets the minutes for the provided datetime (2 char value)
    SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(mi, @DT)) 
                    WHEN 1 
                        then '0' + CONVERT(VARCHAR(2),DATEPART(mi, @DT)) 
                    ELSE CONVERT(VARCHAR(2),DATEPART(mi, @DT)) END
    
    -- This query gets the timezone information
    -- from the TIME_ZONES table for the provided timezone
    SELECT
        @OFFSETHR=offset_hr,
        @OFFSETMI=offset_mi,
        @DSTOFFSETHR=dst_offset_hr,
        @DSTOFFSETMI=dst_offset_mi,
        @DSTEFFDT=dst_eff_dt,
        @DSTENDDT=dst_END_dt
    FROM time_zones
    WHERE timezone_cd = @TZ AND
        @DT BETWEEN eff_dt AND end_dt
    
    -- Checks to see if the DST parameter
    -- for the datetime provided is within the DST 
    -- parameter for the timezone
    IF @DSTDT BETWEEN @DSTEFFDT AND @DSTENDDT
    BEGIN
        -- Increase the datetime by the hours
        -- and minutes assigned to the timezone
        SET @NEWDT = DATEADD(hh,ABS(@DSTOFFSETHR),@DT)
        SET @NEWDT = DATEADD(mi,ABS(@DSTOFFSETMI),@NEWDT)
    END
    -- If the DST parameter for the provided datetime is not within the defined
    -- DST eff and end dates for the timezone then use the standard time offset
    ELSE
    BEGIN
        -- Increase the datetime by the hours
        -- and minutes assigned to the timezone
        SET @NEWDT = DATEADD(hh,ABS(@OFFSETHR),@DT)
        SET @NEWDT = DATEADD(mi,ABS(@OFFSETMI),@NEWDT)
    END

    -- Return the new date that has been converted to UTC time
    RETURN @NEWDT
END

The next function that I needed to create was one that would convert time from UTC time to a specified time zone, similar to the GET_UTCTIME function. This one takes two parameters; the date-time to be converted and the time zone code to convert the provided date-time to. The function declares some variables, then it converts the provided date-time to the DST parameter format (MMWDHHmm). Next, it uses the provided time zone code to get the parameters from the TIME_ZONES table, and populates the variables with the returned values. Last, it checks to see if the provided date-time falls within the daylight range, and then applies the proper offset value, and then returns the adjusted date-time value.

SQL
CREATE FUNCTION GET_TZTIME 
    (@DT AS DATETIME, 
     @TZ AS VARCHAR(12))
RETURNS DATETIME
AS
BEGIN
-- DECLARE VARIABLES
    DECLARE @NEWDT AS DATETIME
    DECLARE @OFFSETHR AS INT
    DECLARE @OFFSETMI AS INT
    DECLARE @DSTOFFSETHR AS INT
    DECLARE @DSTOFFSETMI AS INT
    DECLARE @DSTDT AS VARCHAR(10)
    DECLARE @DSTEFFDT AS VARCHAR(10)
    DECLARE @DSTENDDT AS VARCHAR(10)
    
-- GET THE DST parameter from the provided datetime
    -- This gets the month of the datetime provided (2 char value)
    SELECT @DSTDT = CASE LEN(DATEPART(month, @DT)) 
                WHEN 1 
                    then '0' + CONVERT(VARCHAR(2),DATEPART(month, @DT)) 
                ELSE CONVERT(VARCHAR(2),DATEPART(month, @DT)) END
    -- This gets the occurrence of the day of the week within the month
    --(i.e. first Sunday, or second Sunday...) (1 char value)
    SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),(DATEPART(day,@DT) + 6) / 7)
    -- This gets the day of the week for the provided datetime (1 char value)
    SELECT @DSTDT = @DSTDT + CONVERT(VARCHAR(1),DATEPART(dw, @DT))
    -- This gets the hour for the provided datetime (2 char value)
    SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(hh, @DT)) 
                    WHEN 1 
                        then '0' + CONVERT(VARCHAR(2),DATEPART(hh, @DT)) 
                    ELSE CONVERT(VARCHAR(2),DATEPART(hh, @DT)) END
    -- This gets the minutes for the provided datetime (2 char value)
    SELECT @DSTDT = @DSTDT + CASE LEN(DATEPART(mi, @DT)) 
                    WHEN 1 
                        THEN '0' + CONVERT(VARCHAR(2),DATEPART(mi, @DT)) 
                        ELSE CONVERT(VARCHAR(2),DATEPART(mi, @DT)) END
    
    -- This query gets the timezone information from the TIME_ZONES table
    -- for the provided timezone
    SELECT
        @OFFSETHR=offset_hr,
        @OFFSETMI=offset_mi,
        @DSTOFFSETHR=dst_offset_hr,
        @DSTOFFSETMI=dst_offset_mi,
        @DSTEFFDT=dst_eff_dt,
        @DSTENDDT=dst_END_dt
    FROM time_zones
    WHERE timezone_cd = @TZ AND
        @DT BETWEEN eff_dt AND end_dt
    
    -- Checks to see if the DST parameter for the datetime provided
    -- is within the DST parameter for the timezone
    IF @DSTDT BETWEEN @DSTEFFDT AND @DSTENDDT
    BEGIN
        -- Increase the datetime by the hours and minutes assigned to the timezone
        SET @NEWDT = DATEADD(hh,@DSTOFFSETHR,@DT)
        SET @NEWDT = DATEADD(mi,@DSTOFFSETMI,@NEWDT)
    END
    -- If the DST parameter for the provided datetime is not within the defined
    -- DST eff and end dates for the timezone then use the standard time offset
    ELSE
    BEGIN
        -- Increase the datetime by the hours and minutes assigned to the timezone
        SET @NEWDT = DATEADD(hh,@OFFSETHR,@DT)
        SET @NEWDT = DATEADD(mi,@OFFSETMI,@NEWDT)
    END

    -- Return the new date that has been converted from UTC time
    RETURN @NEWDT
END

Now that I have the two functions to convert time to and from UTC, I can now create the main function that I will use for most queries, NEW_TIME. This function will use the previous two functions to deliver the desired results. This function takes three parameters: the date-time to be converted, the time zone code of the provided date-time, and the time zone to convert the provided date-time to. The function starts by checking to see if the initial time zone code is either UTC or GMT. The reason for this is that if the starting point is one of those, then the function will not need to convert it to UTC time. If the initial time zone is not GMT or UTC, then the provided date time is converted to UTC time using the provided initial time zone code, and that value is applied to the @NEWDT variable. If the code is UTC or GMT, then the @NEWDT variable is set to the provided date-time. Next, the function converts the value in the @NEWDT variable using the GET_TZTIME function and the second time zone code provided, and returns the converted value.

SQL
CREATE FUNCTION NEW_TIME
    (@DT AS DATETIME, 
     @TZ1 AS VARCHAR(12),
     @TZ2 AS VARCHAR(12))
RETURNS DATETIME
AS
BEGIN
    -- Declare variables
    DECLARE @NEWDT AS DATETIME
    
    -- Check to see if the provided timezone
    -- for the source datetime is in GMT or UTC time
    -- If it is not then convert the provided datetime to UTC time
    IF NOT @TZ1 IN ('GMT','UTC')
    BEGIN
        SELECT @NEWDT = dbo.GET_UTCTIME(@DT,@TZ1)
    END
    ELSE
    -- If the provided datetime is in UTC or GMT time
    -- then set the NEWTIME variable to this value
    BEGIN
        SET @NEWDT = @DT
    END

    -- Check to see if the provided conversion timezone is GMT or UTC
    -- If it is then no conversion is needed.
    -- If it is not then convert the provided datetime to the desired timezone
    IF NOT @TZ2 IN ('GMT','UTC')
    BEGIN
        SELECT @NEWDT = dbo.GET_TZTIME(@NEWDT,@TZ2)
    END

    -- Return the new converted datetime
    RETURN @NEWDT
    
END

Final Solution Architecture

The final solution looks like this. We have our local applications with their data stores. We setup database replication so that shortly after the record has been written to the local database, it is replicated to the central data store. The central data store tables have triggers on them that convert the local date-time to UTC time. The records have a field that stores the original local date-time, and another field to store the converted UTC date-time. All of our reports use the UTC time, and the time zone functions to convert the date-time to the desired time zone of the report user.

So far, I have not run into any issues with this setup to require any changes, though I am sure it is not perfect and could use some adjustments here and there. For the application that this was specifically designed for, it has worked out really well.

Points of Interest

The one annoying thing that I have not had a chance to research about is that in order to use the functions, you are required to use the schema owner name prior to the function name (e.g., dbo.NEW_TIME()). I am sure that there is a way around this, but I have not come across it yet, and it can be annoying at times to remember to always use that, since I did not have to when working with Oracle previously, or with other database objects within the MS SQL Server 2005 environment.

Revision History

  • 11-17-2008
    • Original article.
  • 11-30-2008: Based upon a query from user stribbed, I made some modifications to this article - Thanks for the feedback!
    • Added effective and end date fields to the TIME_ZONES table.
    • Modified GET_TZTIME so it only looks for the time zone config that is within the effective and end dates in the TIME_ZONES table.
    • Modified GET_UTCTIME so it only looks for the time zone config that is within the effective and end dates in the TIME_ZONES table.

License

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


Written By
Other
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
kumaranand5610-May-17 3:37
kumaranand5610-May-17 3:37 
QuestionPrecisely the workaround for MS SQL 2005 I needed Pin
eddiemjm432125-Sep-14 23:39
eddiemjm432125-Sep-14 23:39 
SuggestionA new alternative solution for such issues: T-SQL Toolbox on CodePlex Pin
adss20093-May-14 0:53
adss20093-May-14 0:53 
QuestionProblem with Time Zone calculation for this year Pin
Mark Kilroy11-Mar-14 12:02
Mark Kilroy11-Mar-14 12:02 
QuestionTimezones info for Europe Pin
Maikel_D9-Apr-13 21:31
Maikel_D9-Apr-13 21:31 
QuestionExcellent Pin
Patel,N25-Jan-13 10:11
Patel,N25-Jan-13 10:11 
QuestionWhich is better? Pin
AndyTexas15-Nov-10 5:54
AndyTexas15-Nov-10 5:54 
AnswerRe: Which is better? Pin
robertford16-Nov-10 14:19
robertford16-Nov-10 14:19 
GeneralRe: Which is better? Pin
shell_l_d6-Feb-12 17:30
shell_l_d6-Feb-12 17:30 
GeneralSuggested updates Pin
Colin 215-Apr-10 1:17
Colin 215-Apr-10 1:17 
QuestionRe: Suggested updates [modified] Pin
shell_l_d20-Jul-10 16:13
shell_l_d20-Jul-10 16:13 
AnswerRe: Suggested updates Pin
robertford21-Jul-10 3:22
robertford21-Jul-10 3:22 
GeneralUsing the function in very large data sets Pin
robertford11-Jun-09 18:27
robertford11-Jun-09 18:27 
GeneralCode Change Pin
robertford11-Jun-09 18:04
robertford11-Jun-09 18:04 
GeneralGET_TZTIME v3 Pin
robertford11-Jun-09 18:14
robertford11-Jun-09 18:14 
GeneralTime Zone Functions v4 Pin
robertford21-Jan-10 14:35
robertford21-Jan-10 14:35 
GeneralTime Zone Functions v5 Pin
robertford2-Apr-10 18:05
robertford2-Apr-10 18:05 
GeneralRe: Time Zone Functions v5 [modified] Pin
shell_l_d20-Jul-10 19:31
shell_l_d20-Jul-10 19:31 
GeneralRe: Time Zone Functions v5 Pin
robertford21-Jul-10 3:19
robertford21-Jul-10 3:19 
GeneralRe: Time Zone Functions v5 [modified] Pin
shell_l_d21-Jul-10 4:18
shell_l_d21-Jul-10 4:18 
GeneralRe: Time Zone Functions v5 Pin
shell_l_d21-Jul-10 16:13
shell_l_d21-Jul-10 16:13 
AnswerRe: Time Zone Functions v5 [modified] Pin
shell_l_d22-Jul-10 5:09
shell_l_d22-Jul-10 5:09 
Ok, hope you dont mind but I've made some changes & here's the code Wink | ;)

Fixed an issue where if DST Start and DST End, would use same year.
Simplified CalcDaylightSavingsDate (removed DaysInMth case statements, removed most DstModifier code)
Edited TimeZones table, added CHECKS, renamed objects, removed unused variables.

ETA 26Jul: Changed PROCEDURES back to FUNCTIONS.
ETA 29Jul: Added NULL error checking to functions, so handles neatly.


The code itself:

-- *************************
--
-- SQL 2005 Time Zone Conversion Functions - Setup Tables and Functions
--
-----------------------------------------------------------------------------
-- Created by:  Robert Ford per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx
-- Created on:  17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2'   per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3440006#xx3440006xx
-- Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
-----------------------------------------------------------------------------------
--
-- 17Nov2008
--	Creation Date
--
-- 30Nov2008
--	Made a modification based upon a note from an Austrailian user of Code project
--	to have an effective and end date in order to have multiple configurations for a single
--	timezone code.  In Austrailia DST dates change often and would require to have the
--	timezone code updated regularly.
--
-- 11Jun2009
--	Made a modification to fix an issue with the code in situations where the DST end range date is
--	in a month that does not start with Sunday.
--
-- 21Jan2010
--	Fixed an issue with the GetGmtTime function in regards to time zones that are positive GMT adjusted.
--
-- 02Apr2010
--	Fixed an issue where the functions were not evaluating the hour prior to the conversion
--	time.
--
-- 15Apr2010
--	Edited by 'Colin 2' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3440006#xx3440006xx
--	Fixed an issue where the calculate daylight savings date returns incorrect date if need 'Last' wk of mth, added 'L'.
--
-- 22Jul2010
--	Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
--      Fixed an issue where if DST Start > DST End, would use same year.
--      Simplified CalcDaylightSavingsDate (removed DaysInMth case statements, removed most DstModifier code)
--	Edited TimeZones table, added CHECKS, renamed objects, removed unused variables.
--
-- 26Jul2010
--	Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
--	Convert PROCEDURES (Colin 2) back to FUNCTIONS (RobertFord), so can use them in a select statement.
--
-- 29Jul2010
--	Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
--	Add error checking for NULL Date or TimeZone.
--
-- *************************
--
-- Table: 
--	TimeZones
--		tz_Code 		- code for the timezone - used by functions to reference the other values (eg: 'NSW')
--		tz_Description		- name to describe the time zone code            (eg: 'New South Wales AUSTRALIA = GMT +10 = EST/EDT')
--		tz_StartDate		- start date time for timezone (eg: '01 Jul 2008')
--		tz_EndDate		- end   date time for timezone (eg: '30 Jun 9999')
--		tz_OffsetHr		- Standard GMT offset hours for adjusting the date time   (eg: 10)
--		tz_OffsetMins		- Standard GMT offset minutes for adjusting the date time (eg: 0)
--		tz_DstOffsetHr		- Daylight Savings Time GMT offset hours   for adjusting the date time (eg: 11)
--		tz_DstOffsetMins	- Daylight Savings Time GMT offset minutes for adjusting the date time (eg:  0)
--		tz_DstStartMth 		- Daylight Savings Time start month 	(eg: 10 = Oct)
--		tz_DstStartWkOfMth	- Daylight Savings Time start WkOfMth 	(eg: '1'=1st wk, '4'=4th wk, 'L'=Last wk)
--		tz_DstStartDayOfWk	- Daylight Savings Time start DayOfWk 	(eg: 1=Sun, 2=Mon, 7=Sat)
--		tz_DstStartTime		- Daylight Savings Time start Time 	(eg: '02:00' = 2am)
--		tz_DstEndMth 		- Daylight Savings Time end   month 	(eg: 4 = Apr)
--		tz_DstEndWkOfMth 	- Daylight Savings Time end   WkOfMth 	(eg: '1'=1st wk, '4'=4th wk, 'L'=Last wk)
--		tz_DstEndDayOfWk 	- Daylight Savings Time end   DayOfWk 	(eg: 1=Sun, 2=Mon, 7=Sat)
--		tz_DstEndTime		- Daylight Savings Time end   Time 	(eg: '03:00' = 3am)
--	Notes: This table holds the time zone criterion used to convert date time values between GMT TimeZones
--
-- Functions:   NOTE: these assume Sunday is first day of week, so assumes 'set datefirst 7' is set prior.
--
-- 	CalcDaylightSavingsDate
-- 		@TimeZone 	- Time zone to find the Daylight Savings Time (DST) for (value from TimeZones.tz_Code)
-- 		@DstDateType 	- 'Start' to find Start DST or 'End' to find End DST
-- 		@DateToConvert	- UTC or GMT DATETIME to find DST Start or End Date for
-- 		RETURNS DATETIME- The Start or End of DST Time for the specified TimeZone, Type and Date.
--		
--	GetGmtTime
-- 		@FromDate 	- UTC or GMT DATETIME to be converted
-- 		@FromTimeZone 	- Time zone that the date time being passed is in (value from TimeZones.tz_Code)
-- 		RETURNS DATETIME- The converted UTC or GMT DATETIME.
--
--	GetTzTime
-- 		@FromDate 	- UTC or GMT DATETIME to be converted
-- 		@ToTimeZone 	- Time zone to convert the date time to (value from TimeZones.tz_Code)
-- 		RETURNS DATETIME- The converted UTC or GMT DATETIME.
--
--	ConvertTimeZone
-- 		@FromDate 	- UTC or GMT DATETIME to be converted
-- 		@FromTimeZone 	- Time zone that the date time being passed is in (value from TimeZones.tz_Code)
-- 		@ToTimeZone 	- Time zone to convert the date time to (value from TimeZones.tz_Code)
-- 		RETURNS DATETIME- The converted UTC or GMT DATETIME.
--		


-- =============================================
-- Create TABLE (TimeZones)
-- =============================================

-- Check to see if the table already exists and deletes it if it does
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TimeZones]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	DROP TABLE [dbo].[TimeZones]
GO

CREATE TABLE [dbo].[TimeZones] (
	[tz_Code] [VARCHAR] (12) NOT NULL ,
	[tz_Description] [VARCHAR] (60) NOT NULL ,
	[tz_StartDate] DATETIME NOT NULL 	DEFAULT GETDATE(),
	[tz_EndDate] DATETIME NOT NULL 		DEFAULT '31 Dec 9999',
	[tz_OffsetHr] [int] NOT NULL 		DEFAULT -1,
	[tz_OffsetMins] [int] NOT NULL 		DEFAULT  0,
	[tz_DstOffsetHr] [int] 			DEFAULT NULL ,
	[tz_DstOffsetMins] [int] 		DEFAULT NULL ,
	[tz_DstStartMth] [int]	 		DEFAULT NULL 	CHECK ( [tz_DstStartMth] IS NULL OR [tz_DstStartMth] BETWEEN 1 and 12 ) ,
	[tz_DstStartWkOfMth] [VARCHAR] (1) 	DEFAULT NULL 	CHECK ( [tz_DstStartWkOfMth] in (NULL,'1','2','3','4','L') ) ,
	[tz_DstStartDayOfWk] [int] 		DEFAULT NULL 	CHECK ( [tz_DstStartDayOfWk] IS NULL OR [tz_DstStartDayOfWk] BETWEEN 1 and 7 ) ,
	[tz_DstStartTime] [DATETIME] 		DEFAULT NULL ,
	[tz_DstEndMth] [int] 			DEFAULT NULL 	CHECK ( [tz_DstEndMth] IS NULL OR [tz_DstEndMth] BETWEEN 1 and 12 ) ,
	[tz_DstEndWkOfMth] [VARCHAR] (1) 	DEFAULT NULL 	CHECK ( [tz_DstEndWkOfMth] in (NULL,'1','2','3','4','L') ) ,
	[tz_DstEndDayOfWk] [int] 		DEFAULT NULL 	CHECK ( [tz_DstEndDayOfWk] IS NULL OR [tz_DstEndDayOfWk] BETWEEN 1 and 7 ) ,
	[tz_DstEndTime] [DATETIME] 		DEFAULT NULL ,
	CONSTRAINT [PK_TimeZones] PRIMARY KEY ([tz_Code],[tz_StartDate])
)
GO


-- =============================================
-- Insert Into TimeZones Table
-- =============================================

-- TimeZones from approx 1987

INSERT INTO TimeZones 
SELECT 'AZ','Arizona USA = GMT -7 = MST','01 Jul 1980','30 Jun 9999',-7,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL
SELECT 'BALIK','Balikpapan INDONESIA = GMT +8 = WITA','01 Jul 1980','30 Jun 9999',8,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL
SELECT 'BEIJING','Beijing CHINA = GMT +8 = CST','01 Jul 1980','30 Jun 9999',8,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL
SELECT 'JHB','Johannesburg SOUTH AFRICA = GMT +2 = SAST','01 Jul 1980','30 Jun 9999',2,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL
SELECT 'LIMA','Lima PERU = GMT -5 = PET','01 Jul 1980','30 Jun 9999',-5,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL
SELECT 'PUNE_MH','Pune Maharashtra INDIA = GMT +5.5 = IST','01 Jul 1980','30 Jun 9999',5,30,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
GO

INSERT INTO TimeZones 
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 1987','30 Jun 1995',10,0,11,0,10,'L',1,'02:00', 3,'3',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 1989','30 Jun 1995',10,0,11,0,10,'L',1,'02:00', 3,'1',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 1995','30 Jun 2000',10,0,11,0,10,'L',1,'02:00', 3,'L',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 2000','01 Sep 2001',10,0,11,0, 8,'L',1,'02:00', 3,'L',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 2001','30 Jun 2005',10,0,11,0,10,'L',1,'02:00', 3,'L',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 2005','30 Jun 2006',10,0,11,0,10,'L',1,'02:00', 4,'1',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 2006','30 Jun 2007',10,0,11,0,10,'L',1,'02:00', 3,'L',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 2007','30 Jun 2008',10,0,11,0,10,'L',1,'02:00', 4,'1',1,'03:00'
UNION ALL
SELECT 'NSW','New South Wales AUSTRALIA = GMT +10 = EST/EDT','01 Jul 2008','30 Jun 9999',10,0,11,0,10,'1',1,'02:00', 4,'1',1,'03:00'
UNION ALL
SELECT 'PC_BC','Port Coquitlam BC CANADA = GMT -8 = PST/PDT','01 Jan 1987','31 Dec 2006',-8,0,-7,0, 4,'1',1,'02:00',10,'L',1,'02:00'
UNION ALL
SELECT 'PC_BC','Port Coquitlam BC CANADA = GMT -8 = PST/PDT','01 Jan 2007','31 Dec 9999',-8,0,-7,0, 3,'2',1,'02:00',11,'1',1,'02:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1986','30 Jun 1987',-4,0,-3,0,10,'2',1,'00:00', 4,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1987','30 Jun 1988',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1988','30 Jun 1989',-4,0,-3,0,10,'1',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1989','30 Jun 1990',-4,0,-3,0,10,'3',1,'00:00', 3,'3',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1990','30 Jun 1991',-4,0,-3,0, 9,'3',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1991','30 Jun 1992',-4,0,-3,0,10,'2',1,'00:00', 3,'3',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1992','30 Jun 1996',-4,0,-3,0,10,'3',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1996','30 Jun 1997',-4,0,-3,0,10,'2',1,'00:00', 3,'L',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1997','30 Jun 1998',-4,0,-3,0,10,'2',1,'00:00', 3,'3',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1998','30 Jun 1999',-4,0,-3,0,09,'4',1,'00:00', 4,'1',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 1999','30 Jun 2000',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2000','30 Jun 2001',-4,0,-3,0,10,'3',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2001','30 Jun 2001',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2005','30 Jun 2006',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2006','30 Jun 2007',-4,0,-3,0,10,'3',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2007','30 Jun 2008',-4,0,-3,0,10,'2',1,'00:00', 3,'L',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2008','30 Jun 2009',-4,0,-3,0,10,'2',1,'00:00', 3,'3',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2009','30 Jun 2010',-4,0,-3,0,10,'2',1,'00:00', 4,'1',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2010','30 Jun 2014',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2014','30 Jun 2015',-4,0,-3,0,10,'2',1,'00:00', 3,'3',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2015','30 Jun 2017',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2017','30 Jun 2018',-4,0,-3,0,10,'3',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'AGO','Santiago CHILE = GMT -4 = CLST/CLT','01 Jul 2018','30 Jun 9999',-4,0,-3,0,10,'2',1,'00:00', 3,'2',1,'00:00'
UNION ALL
SELECT 'MOSCOW','Moscow RUSSIA = GMT +3 = MSK/MSD','01 Jan 1985','31 Dec 1990',3,0,4,0, 3,'L',1,'02:00', 9,'3',1,'03:00'
UNION ALL
SELECT 'MOSCOW','Moscow RUSSIA = GMT +3 = MSK/MSD','01 Jan 1991','31 Aug 1991',3,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
UNION ALL
SELECT 'MOSCOW','Moscow RUSSIA = GMT +3 = EEST/EET/MSK','01 Sep 1991','31 Jan 1992',3,0,2,0, 9,'L',1,'03:00', 1,'3',1,'02:00'
UNION ALL
SELECT 'MOSCOW','Moscow RUSSIA = GMT +3 = MSK/MSD','01 Feb 1992','31 Dec 1992',3,0,4,0, 3,'L',7,'00:00', 9,'L',7,'23:00'
UNION ALL
SELECT 'MOSCOW','Moscow RUSSIA = GMT +3 = MSK/MSD','01 Jan 1992','31 Dec 1992',3,0,4,0, 3,'L',7,'23:00', 9,'L',7,'23:00'
UNION ALL
SELECT 'MOSCOW','Moscow RUSSIA = GMT +3 = MSK/MSD','01 Jan 1993','31 Dec 9999',3,0,4,0, 3,'L',1,'02:00',10,'L',1,'03:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1987','30 Jun 1988',-3,0,-2,0,10,'3',1,'00:00', 1,'L',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1988','30 Jun 1989',-3,0,-2,0,10,'3',1,'00:00', 2,'2',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1989','30 Jun 1990',-3,0,-2,0,10,'3',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1991','30 Jun 1992',-3,0,-2,0,10,'3',1,'00:00', 2,'2',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1992','30 Jun 1993',-3,0,-2,0,10,'L',1,'00:00', 1,'L',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1993','30 Jun 1995',-3,0,-2,0,10,'3',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1995','30 Jun 1996',-3,0,-2,0,10,'3',1,'00:00', 2,'2',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1996','30 Jun 1997',-3,0,-2,0,10,'1',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1997','30 Jun 1998',-3,0,-2,0,10,'1',1,'00:00', 2,'4',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1998','30 Jun 1999',-3,0,-2,0,10,'2',1,'00:00', 2,'2',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 1999','30 Jun 2000',-3,0,-2,0,10,'1',1,'00:00', 2,'4',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2000','30 Jun 2002',-3,0,-2,0,10,'2',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2002','30 Jun 2003',-3,0,-2,0,11,'1',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2003','30 Jun 2004',-3,0,-2,0,10,'3',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2004','30 Jun 2005',-3,0,-2,0,11,'1',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2005','30 Jun 2006',-3,0,-2,0,10,'3',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2006','30 Jun 2007',-3,0,-2,0,11,'1',1,'00:00', 2,'4',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2007','30 Jun 2008',-3,0,-2,0,10,'2',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2008','30 Jun 2009',-3,0,-2,0,10,'3',1,'00:00', 2,'2',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2009','30 Jun 2011',-3,0,-2,0,10,'3',1,'00:00', 2,'3',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2011','30 Jun 2012',-3,0,-2,0,10,'3',1,'00:00', 2,'4',1,'00:00'
UNION ALL
SELECT 'MG','Minas Gerais BRAZIL = GMT -3 = BRST/BRT','01 Jul 2012','30 Jun 9999',-3,0,-2,0,10,'3',1,'00:00', 2,'3',1,'00:00'
GO

SELECT *
FROM TimeZones
GO

-- =============================================
-- Create FUNCTION (CalcDaylightSavingsDate)
-- =============================================

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'CalcDaylightSavingsDate')
	DROP FUNCTION CalcDaylightSavingsDate
GO

CREATE FUNCTION [dbo].[CalcDaylightSavingsDate]
		(@TimeZone AS VARCHAR(12),
  		 @DstDateType AS VARCHAR(5),
		 @DateToConvert AS DATETIME)
RETURNS DATETIME 
AS
BEGIN
-----------------------------------------------------------------------------
-- Created by:  Robert Ford per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx
-- Created on:  17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2'   per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3440006#xx3440006xx
-- Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
-----------------------------------------------------------------------------------
-- @TimeZone 		- Time zone to find the Daylight Savings Time (DST) for (value from TimeZones.tz_Code)
-- @DstDateType 	- 'Start' to find Start DST or 'End' to find End DST
-- @DateToConvert	- UTC or GMT DATETIME to find DST Start or End Date for
-- RETURNS DATETIME 	- The Start or End of DST Time for the specified TimeZone, Type and Date.
--
-- NOTE: assumes Sunday is first day of week, so assumes 'set datefirst 7' is set prior.
--       Constructs a DATETIME from a TimeZone & DateTime because Daylight Savings Time (DST) starts/ends on a different
--       date every year (e.g. last Sunday in March is start of DST in Central European Timezone). 
--       Uses DATETIME functions to use first day of year and first day of month DST changes & finally determines correct day.
-----------------------------------------------------------------------------

	-- Declare Variables
	DECLARE @DstOffsetHr AS INT
	DECLARE @DstOffsetMins AS INT
	DECLARE @DstMthIncrement AS INT
	DECLARE @DstWkOfMth AS VARCHAR(1)
	DECLARE @DstWkOfMthInt AS INT
	DECLARE @DstDayOfWk AS INT
	DECLARE @DstHr INT
	DECLARE @DstMins INT
	DECLARE @DstEndsNextYr INT
	DECLARE @DstTempDate DATETIME 	--Holding variable for constructing the date.
	DECLARE @DaysInMth AS INT
	DECLARE @DstModifier INT

	-- Abort if DateToConvert or TimeZone is NULL or DstDateType not 'Start' or 'End'
	IF @DateToConvert IS NULL or @TimeZone IS NULL or @DstDateType NOT IN ('Start', 'End')
		RETURN NULL

	--Populate variables. This is depended on whether we are trying to find DST start or end
	--in a given timezone relative to a given date.	
	SELECT
		@DstOffsetHr = tz_DstOffsetHr,
		@DstOffsetMins = tz_DstOffsetMins,
		@DstMthIncrement = CASE @DstDateType WHEN 'Start' THEN tz_DstStartMth - 1
						     WHEN 'End'   THEN tz_DstEndMth   - 1
						     END,
		@DstWkOfMth = CASE @DstDateType WHEN 'Start' THEN tz_DstStartWkOfMth
				 		WHEN 'End'   THEN tz_DstEndWkOfMth
						END,
		@DstDayOfWk = CASE @DstDateType WHEN 'Start' THEN tz_DstStartDayOfWk
				 		WHEN 'End'   THEN tz_DstEndDayOfWk
						END,
		@DstHr 	= CASE @DstDateType WHEN 'Start' THEN DATEPART( hh, tz_DstStartTime )
				 	    WHEN 'End'   THEN DATEPART( hh, tz_DstEndTime   )
					    END,
		@DstMins 	= CASE @DstDateType WHEN 'Start' THEN DATEPART( mi, tz_DstStartTime )
			 			    WHEN 'End'   THEN DATEPART( mi, tz_DstEndTime   )
						    END,
		@DstEndsNextYr  = CASE WHEN tz_DstStartMth > tz_DstEndMth THEN 1 ELSE 0 END
	FROM TimeZones
	WHERE tz_Code = @TimeZone AND
		@DateToConvert BETWEEN tz_StartDate AND tz_EndDate

	-- It is possible for the DST code occurrence day to be passed as 'L', indicating the last day (usually Sunday) of a given month.
	-- We need the integer variable for calcalations relating to 1 - 4.
	IF @DstWkOfMth <> 'L'
		SET @DstWkOfMthInt = CONVERT( INT, @DstWkOfMth ) 
	ELSE
		SET @DstWkOfMthInt = 4

	-- Perform calculations to determine date
	-- Set the holding date variable to the first day of the year for the year of the date being evaluated
	SET @DstTempDate = CONVERT( DATETIME, '1/1/' + CONVERT( VARCHAR(4), DATEPART(year,@DateToConvert) ) )

	-- Handle scenario where DST ends in the next year (eg: AUSTRALIA)
	IF @DstDateType = 'End' AND @DstEndsNextYr = 1 
		SET @DstTempDate = DATEADD( year, 1, @DstTempDate )
	
	-- Add month value for DST pattern to the holding date variable
	SET @DstTempDate = DATEADD( month, @DstMthIncrement, @DstTempDate )
	
	-- #days in month is: construct 1st of month (above), add 1 month and subtract one day. 
	-- This identifies the last day of the month which is also the number of days.
	set @DaysInMth = convert( int, DATENAME( DAY, DATEADD(DAY,-1,DATEADD(Month,1,@DstTempDate) ) ) )

	-- Determine the modifier value needed to adjust the date
	SET @DstModifier = DATEPART(weekday,@DstTempDate) - 1

	-- This is the main calculation to determine the DST date 
	SET @DstTempDate = DATEADD( day, (@DstWkOfMthInt*7)-@DstModifier, @DstTempDate )
	SET @DstTempDate = DATEADD( hour, @DstHr, @DstTempDate )
	SET @DstTempDate = DATEADD( minute, @DstMins, @DstTempDate )

	-- Some of the days of the week occur five times in any given month. Adjust if needed.
	IF @DstWkOfMth = 'L' AND ( @DaysInMth - DATEPART( day, @DstTempDate ) >= 7 )
		SET @DstTempDate = DATEADD( day, 7, @DstTempDate )

	RETURN @DstTempDate
END
GO


-- =============================================
-- Create STORED PROCEDURE (GetGmtTime)
-- =============================================

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'GetGmtTime')
	DROP FUNCTION GetGmtTime
GO

CREATE FUNCTION [dbo].[GetGmtTime]
		(@FromDate AS DATETIME,
 		 @FromTimeZone AS VARCHAR(12))
RETURNS DATETIME 
AS
BEGIN
-----------------------------------------------------------------------------
-- Created by:  Robert Ford per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx
-- Created on:  17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2'   per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3440006#xx3440006xx
-- Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
-----------------------------------------------------------------------------------
-- @FromDate 		- UTC or GMT DATETIME to be converted
-- @FromTimeZone 	- Time zone that the date time being passed is in (value from TimeZones.tz_Code)
-- RETURNS DATETIME 	- The converted UTC or GMT DATETIME.
--
-- NOTE: assumes Sunday is first day of week, so assumes 'set datefirst 7' is set prior.
-----------------------------------------------------------------------------

	-- Reqd for datepart in this and child procs, called functions assume that the first day of the week is a Sunday
	-- Set Sunday as first Day of the wk
	--SET DATEFIRST 7		-- CAN NOT DO THIS IN A FUNCTION

	--Declare variables
	DECLARE @OffsetHr AS INT
	DECLARE @OffsetMins AS INT
	DECLARE @DstOffsetHr AS INT
	DECLARE @DstOffsetMins AS INT
	DECLARE @DstStartDate AS DATETIME
	DECLARE @DstEndDate AS DATETIME
	DECLARE @NewDate DATETIME

	-- Default to @FromDate
	SET @NewDate = @FromDate

	-- Abort conversion if FromDate or FromTimeZone is NULL
	IF @FromDate IS NULL or @FromTimeZone IS NULL
		RETURN @NewDate

	-- This query gets the timezone information from the TimeZones table for the provided timezone
	SELECT
		@OffsetHr=tz_OffsetHr,
		@OffsetMins=tz_OffsetMins,
		@DstOffsetHr=tz_DstOffsetHr,
		@DstOffsetMins=tz_DstOffsetMins
	FROM TimeZones
	WHERE tz_Code = @FromTimeZone AND
		@FromDate BETWEEN tz_StartDate AND tz_EndDate

	-- Increase the DATETIME by the hours and minutes assigned to the timezone
	-- As the procedure converts TO GMT, the offset in the timezone table needs to be reversed
	-- as they assume a conversion from GMT.
	SET @NewDate = DATEADD( hh, @OffsetHr  *-1, @FromDate )
	SET @NewDate = DATEADD( mi, @OffsetMins*-1, @NewDate  )

	-- Get DST Start & End Dates
	SELECT @DstStartDate = dbo.CalcDaylightSavingsDate( @FromTimeZone, 'Start', @FromDate )
	SELECT @DstEndDate   = dbo.CalcDaylightSavingsDate( @FromTimeZone, 'End',   @FromDate )

	-- Check to see if the date being evaluated falls between the
 	-- DST Start and End date/times
	IF @FromDate BETWEEN @DstStartDate AND DATEADD( hour, -1, DATEADD(second,-1,@DstEndDate) )
	BEGIN
		SET @NewDate = DATEADD( hh, @DstOffsetHr  *-1, @FromDate )
		SET @NewDate = DATEADD( mi, @DstOffsetMins*-1, @NewDate  )
	END

	RETURN @NewDate
END
GO

-- =============================================
-- Create STORED PROCEDURE (GetTzTime)
-- =============================================
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'GetTzTime')
	DROP FUNCTION GetTzTime
GO

CREATE FUNCTION [dbo].[GetTzTime]
		(@FromDate AS DATETIME,
 	 	 @ToTimeZone AS VARCHAR(12))
RETURNS DATETIME
AS
BEGIN
-----------------------------------------------------------------------------
-- Created by:  Robert Ford per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx
-- Created on:  17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2'   per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3440006#xx3440006xx
-- Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
-----------------------------------------------------------------------------------
-- @FromDate 		- UTC or GMT DATETIME to be converted
-- @ToTimeZone 		- Time zone to convert the date time to (value from TimeZones.tz_Code)
-- RETURNS DATETIME 	- The converted UTC or GMT DATETIME.
--
-- NOTE: assumes Sunday is first day of week, so assumes 'set datefirst 7' is set prior.
-----------------------------------------------------------------------------

	-- Reqd for datepart in this and child procs, called functions assume that the first day of the week is a Sunday
	-- Set Sunday as first Day of the wk
	--SET DATEFIRST 7		-- CAN NOT DO THIS IN A FUNCTION

	-- Declare Variables
	DECLARE @OffsetHr AS INT
	DECLARE @OffsetMins AS INT
	DECLARE @DstOffsetHr AS INT
	DECLARE @DstOffsetMins AS INT
	DECLARE @DstStartDate AS DATETIME
	DECLARE @DstEndDate AS DATETIME
	DECLARE @NewDate AS DATETIME

	-- Default to @FromDate
	SET @NewDate = @FromDate

	-- Abort conversion if FromDate or ToTimeZone is NULL
	IF @FromDate IS NULL or @ToTimeZone IS NULL
		RETURN @NewDate

	-- This query gets the timezone information from the TimeZones table for the provided timezone
	SELECT
		@OffsetHr=tz_OffsetHr,
		@OffsetMins=tz_OffsetMins,
		@DstOffsetHr=tz_DstOffsetHr,
		@DstOffsetMins=tz_DstOffsetMins
	FROM TimeZones
	WHERE tz_Code = @ToTimeZone AND
		@FromDate BETWEEN tz_StartDate AND tz_EndDate

	-- Increase the DATETIME by the hours and minutes assigned to the timezone
	-- As the procedure converts FROM GMT, the offset in the timezone table can be used directly.
	SET @NewDate = DATEADD( hh, @OffsetHr,   @FromDate )
	SET @NewDate = DATEADD( mi, @OffsetMins, @NewDate  )

	-- Get DST Start & End Dates
	SELECT @DstStartDate = dbo.CalcDaylightSavingsDate( @ToTimeZone, 'Start', @FromDate )
	SELECT @DstEndDate   = dbo.CalcDaylightSavingsDate( @ToTimeZone, 'End',   @FromDate )

	-- Check to see if the date being evaluated falls between the
	-- DST Start and End date/times
	IF @NewDate BETWEEN @DstStartDate AND DATEADD(hour,-1,DATEADD(second,-1,@DstEndDate))
	BEGIN
		SET @NewDate = DATEADD( hh, @DstOffsetHr,   @FromDate )
		SET @NewDate = DATEADD( mi, @DstOffsetMins, @NewDate  )
	END

	RETURN @NewDate
END
GO

-- =============================================
-- Create STORED PROCEDURE (ConvertTimeZone)
-- =============================================
IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ConvertTimeZone')
	DROP FUNCTION ConvertTimeZone
GO

CREATE FUNCTION [dbo].[ConvertTimeZone] 
 	(@FromDate AS DATETIME,
	 @FromTimeZone AS VARCHAR(12),
 	 @ToTimeZone AS VARCHAR(12))
RETURNS DATETIME
AS
BEGIN
-----------------------------------------------------------------------------
-- Created by:  Robert Ford per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx
-- Created on:  17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2'   per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3440006#xx3440006xx
-- Edited by 'shell_l_d' per http://www.codeproject.com/KB/database/SQL2005_time_zones.aspx?msg=3542594#xx3542594xx
-----------------------------------------------------------------------------------
-- @FromDate 		- UTC or GMT DATETIME to be converted
-- @FromTimeZone 	- Time zone that the date time being passed is in (value from TimeZones.tz_Code)
-- @ToTimeZone 		- Time zone to convert the date time to (value from TimeZones.tz_Code)
-- RETURNS DATETIME 	- The converted UTC or GMT DATETIME.
--
-- NOTE: assumes Sunday is first day of week, so assumes 'set datefirst 7' is set prior.
--       This function uses GetGmtTime and GetTzTime functions to prepare the value that needs to be delivered.  
--       Supports the use of both GMT and UTC codes to represent Universal Time Code or Greenwich Mean Time.
-----------------------------------------------------------------------------

	-- Declare Variables
	DECLARE @NewDate AS DATETIME

	-- Default to @FromDate
	SET @NewDate = @FromDate

	-- Abort conversion if FromDate, FromTimeZone or ToTimeZone is NULL
	IF @FromDate IS NULL or @FromTimeZone IS NULL or @ToTimeZone IS NULL
		RETURN @NewDate

	-- If FromTimeZone is not 'GMT' or 'UTC', then convert it to 'GMT'
	IF NOT @FromTimeZone IN ('GMT','UTC')
		select @NewDate = dbo.GetGmtTime( @FromDate, @FromTimeZone )

	-- If ToTimeZone is NOT 'GMT' or 'UTC', then convert NewDate to the desired timezone
	IF NOT @ToTimeZone IN ('GMT','UTC')
		select @NewDate = dbo.GetTzTime( @NewDate, @ToTimeZone )

	RETURN @NewDate
END
GO



AND some tests...

-- TimeZone Conversion Tests
-- Use www.timeanddate.com - TimeZone conversion utility & to see DST times since 1980 (ish)

/*
SELECT 
	left(tz_startdate,11) as startdt,
	left(tz_enddate,11) as enddt, 
	tz_dststartmth,
	tz_dststartwkofmth,
	tz_dstendmth,
	tz_dstendwkofmth
FROM TimeZones 
where tz_code = 'NSW'
*/


--error checking for NULL's
SELECT dbo.ConvertTimeZone( NULL,NULL,NULL ), NULL
SELECT dbo.ConvertTimeZone( NULL,'NSW','AZ' ), NULL
SELECT dbo.ConvertTimeZone( '21 Nov 2010 23:45:00', NULL, 'AZ' ), '21 Nov 2010 23:45:00'
SELECT dbo.ConvertTimeZone( '21 Nov 2010 23:45:00', 'NSW', NULL ), '21 Nov 2010 23:45:00'

--21Nov2010 23:45 NSW > 21Nov2010 12:45 GMT > 21Nov2010  5:45 AZ
SELECT dbo.ConvertTimeZone( '21 Nov 2010 23:45:00','NSW','AZ' ), '21 Nov 2010 05:45'

--21Jul2010 23:45 NSW > 21Jul2010 13:45 GMT > 21Jul2010  6:45 AZ
SELECT dbo.ConvertTimeZone( '21 Jul 2010 23:45:00','NSW','AZ' ), '21 Nov 2010 06:45'


DECLARE @FromTimeZone AS VARCHAR(12)
DECLARE @FromDate AS DATETIME

SET @FromTimeZone = (SELECT 'NSW')

SET @FromDate='5 Jul 2001 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '28 Oct 2001'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '31 Mar 2002'

SET @FromDate='5 Jul 2002 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '27 Oct 2002'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '30 Mar 2003'

SET @FromDate='5 Jul 2003 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '26 Oct 2003'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '28 Mar 2004'

SET @FromDate='5 Jul 2004 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '31 Oct 2004'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '27 Mar 2005'

SET @FromDate='5 Jul 2005 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '30 Oct 2005'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '2 Apr 2006'

SET @FromDate='5 Jul 2006 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '29 Oct 2006'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '25 Mar 2007'

SET @FromDate='5 Jul 2007 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '28 Oct 2007'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '6 Apr 2008'

SET @FromDate='5 Jul 2008 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '5 Oct 2008'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '5 Apr 2009'

SET @FromDate='5 Jul 2009 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '4 Oct 2009'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '4 Apr 2010'

SET @FromDate='5 Jul 2010 14:00'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'Start', @FromDate), '3 Oct 2010'
SELECT dbo.CalcDaylightSavingsDate(@FromTimeZone, 'End',   @FromDate), '3 Apr 2011'


modified on Wednesday, August 4, 2010 10:56 PM

GeneralRe: Time Zone Functions v5 Pin
Callon Campbell4-Aug-10 4:22
Callon Campbell4-Aug-10 4:22 
GeneralRe: Time Zone Functions v5 [modified] Pin
shell_l_d4-Aug-10 17:42
shell_l_d4-Aug-10 17:42 
GeneralRe: Time Zone Functions v5 Pin
robertford6-Aug-10 5:53
robertford6-Aug-10 5:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.