|
Rahul pointed out an issue with the GET_UTCTIME function. I fixed the code and I am reposting it below.
--
-- SQL 2005 Time Zone Conversion setup tables and functions
--
-- Created by: Robert Ford
-- Created on: 11/17/2008
-- Modified on:01/21/2010
--
-- 11/17/2008
-- Creation Date
--
-- 11/30/2008
-- 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.
--
-- 06/11/2009
-- 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.
--
-- 01/21/2010
-- Fixed an issue with the GET_UTCTIME function in regards to time zones that are positive GMT adjusted.
--
-- *************************
--
-- Tables:
-- TIME_ZONES
-- TIMEZONE_CD VARCHAR(6) NOT NULL - this value is used by the functions ot reference the other values
-- TIMEZONE_NAME VARCHAR(60) NOT NULL - name to describe the time zone code
-- OFFSET_HR INT NOT NULL - the offset hours for adjusting the date time
-- OFFSET_MI INT NOT NULL - the offset minutes for adjusting the date time
-- DST_OFFSET_HR INT NOT NULL - the day light offset hours for adjusting the date time
-- DST_OFFSET_MI INT NOT NULL - the day light offset minutes for adjusting the date time
-- DST_EFF_DT VARCHAR(10) NOT NULL - the day light savings effective date time formate: MMWDHHmm
-- DST_END_DT VARCHAR(10) NOT NULL - the day light savings end date time: MMWDHHmm
-- Notes: This table holds the time zone creterion used to convert date time values
-- DST EFF and END date format: MMWDHHmm
-- MM = two digit month (e.g. March = 3)
-- 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
--
-- Functions:
-- GET_UTCTIME
-- @DT DATETIME - LOCAL DATETIME
-- @TZ VARCHAR(12) - Time Zone code (must match a value in the database table)
-- Notes: Pass the time zone code and date and the function will
-- return a the date in UTC or GMT datetime
-- GET_TZTIME
-- @DT DATETIME - UTC or GMT DATETIME
-- @TZ VARCHAR(12) - Time Zone code (must match a value in the database table)
-- Notes: Pass the GMT or UTC date time and the time zone code
-- and the function will provide the date time adjusted for the specified
-- time zone.
--
-- NEW_TIME
-- @DT DATETIME - DATETIME to be converted
-- @TZ1 VARCHAR(12) - Time zone that the date time being passed is in
-- @TZ2 VARCHAR(12) - Time zone to convert the date time to
-- Note: Pass the date time, the time zone code that the date time is in,
-- and the time zone code that the date time should be converted to.
-- This function uses the GET_UTCTIME and GET_TZTIME 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.
--
--
-- 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].[TIME_ZONES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TIME_ZONES]
GO
-- Create the TIME_ZONES table
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
) ON [PRIMARY]
GO
-- Alter the table to add the primary key
ALTER TABLE [dbo].[TIME_ZONES] WITH NOCHECK ADD
CONSTRAINT [PK_TIME_ZONES] PRIMARY KEY CLUSTERED
(
[TIMEZONE_CD],
[EFF_DT]
) ON [PRIMARY]
GO
-- Add the field default contraints
ALTER TABLE [dbo].[TIME_ZONES] ADD
CONSTRAINT [DF_TIME_ZONES_OFFSET] DEFAULT ((-1)) FOR [OFFSET_HR],
CONSTRAINT [DF_TIME_ZONES_OFFSET_MI] DEFAULT (0) FOR [OFFSET_MI],
CONSTRAINT [DF_TIME_ZONES_DST_OFFSET] DEFAULT ((-1)) FOR [DST_OFFSET_HR],
CONSTRAINT [DF_TIME_ZONES_DST_OFFSET_MI] DEFAULT (0) FOR [DST_OFFSET_MI],
CONSTRAINT [DF_TIME_ZONES_DST_EFF_DT] DEFAULT ('03210200') FOR [DST_EFF_DT],
CONSTRAINT [DF_TIME_ZONES_DST_END_DT] DEFAULT ('11110200') FOR [DST_END_DT],
CONSTRAINT DF_TIME_ZONES_EFF_DT DEFAULT GETDATE() FOR [EFF_DT],
CONSTRAINT DF_TIME_ZONES_END_DT DEFAULT '12/31/9999' FOR [END_DT]
GO
-- Insert time zone information into the TIME_ZONES table
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('AKT','Alaskan Time',-9,0,-8,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('AT','Atlantic Time',-4,0,-3,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('AZ','Arizona Time',-7,0,-7,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('CT','Central Time',-6,0,-5,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('ET','Eastern Time',-5,0,-4,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('HT','Hawaii Time',-10,0,-10,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('MT','Mountain Time',-7,0,-6,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('NT','Newfoundland Time',-4,30,-2,30,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('PT','Pacific Time',-8,0,-7,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('YT','Yukon Time',-8,0,-8,0,'03210200','11110200')
GO
-- =============================================
-- Create scalar function (GET_UTCTIME)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'GET_UTCTIME')
DROP FUNCTION GET_UTCTIME
GO
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 occurance 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*-1,@DT)
SET @NEWDT = DATEADD(mi,@DSTOFFSETMI*-1,@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*-1,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI*-1,@NEWDT)
END
-- Return the new date that has been converted to UTC time
RETURN @NEWDT
END
GO
-- =============================================
-- Create scalar function (GET_TZTIME)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'GET_TZTIME')
DROP FUNCTION GET_TZTIME
GO
CREATE FUNCTION [dbo].[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)
DECLARE @DSTSTARTDT AS DATETIME
DECLARE @DSTSTOPDT AS DATETIME
-- 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
-- Increase the datetime by the hours and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,@OFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI,@NEWDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST start date
DECLARE @DSTSTARTMONTH INT
DECLARE @DSTSTARTINSTANCE INT
DECLARE @DSTSTARTDAY INT
DECLARE @DSTSTARTHOUR INT
DECLARE @DSTSTARTMINUTE INT
DECLARE @DSTSTARTMODIFIER INT
-- Perform calculations to determine date
SET @DSTSTARTMONTH = CONVERT(INT,SUBSTRING(@DSTEFFDT,1,2))-1
SET @DSTSTARTINSTANCE = CONVERT(INT,SUBSTRING(@DSTEFFDT,3,1))
SET @DSTSTARTDAY = CONVERT(INT,SUBSTRING(@DSTEFFDT,4,1))
SET @DSTSTARTHOUR = CONVERT(INT,SUBSTRING(@DSTEFFDT,5,2))
SET @DSTSTARTMINUTE = CONVERT(INT,SUBSTRING(@DSTEFFDT,7,2))
-- Set the start date variable to the first day of the year for the year of the date being evaluated
SET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Add month value for start pattern to the start date variable
SET @DSTSTARTDT = DATEADD(month,@DSTSTARTMONTH,@DSTSTARTDT)
-- Determine the modifier value needed to adjust the date when the date desired is in a
-- month that does not start on a Sunday
SET @DSTSTARTMODIFIER = DATEPART(weekday,@DSTSTARTDT)
-- Check to see if the modifier date is greater than the date being evaluated
-- This is important because it will change the final modifier
-- (adding days in some instances and subtracting days in others
IF @DSTSTARTMODIFIER > @DSTSTARTDAY
BEGIN
SET @DSTSTARTMODIFIER = 8 - @DSTSTARTMODIFIER
END
ELSE
BEGIN
SET @DSTSTARTMODIFIER = (@DSTSTARTMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTSTARTDT = DATEADD(day,(@DSTSTARTINSTANCE*7)-(8-@DSTSTARTDAY) + @DSTSTARTMODIFIER,@DSTSTARTDT)
SET @DSTSTARTDT = DATEADD(hour,@DSTSTARTHOUR,@DSTSTARTDT)
SET @DSTSTARTDT = DATEADD(minute,@DSTSTARTMINUTE,@DSTSTARTDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST stop date
DECLARE @DSTSTOPMONTH INT
DECLARE @DSTSTOPINSTANCE INT
DECLARE @DSTSTOPDAY INT
DECLARE @DSTSTOPHOUR INT
DECLARE @DSTSTOPMINUTE INT
DECLARE @DSTSTOPMODIFIER INT
-- Perform calculations to determine date
SET @DSTSTOPMONTH = CONVERT(INT,SUBSTRING(@DSTENDDT,1,2))-1
SET @DSTSTOPINSTANCE = CONVERT(INT,SUBSTRING(@DSTENDDT,3,1))
SET @DSTSTOPDAY = CONVERT(INT,SUBSTRING(@DSTENDDT,4,1))
SET @DSTSTOPHOUR = CONVERT(INT,SUBSTRING(@DSTENDDT,5,2))
SET @DSTSTOPMINUTE = CONVERT(INT,SUBSTRING(@DSTENDDT,7,2))
-- Set the start date variable to the first day of the year for the year of the date being evaluated
SET @DSTSTOPDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Determine the modifier value needed to adjust the date when the date desired is in a
SET @DSTSTOPDT = DATEADD(month,@DSTSTOPMONTH,@DSTSTOPDT)
-- Determine the modifier value needed to adjust the date when the date desired is in a
-- month that does not start on a Sunday
SET @DSTSTOPMODIFIER = DATEPART(weekday,@DSTSTOPDT)
-- Check to see if the modifier date is greater than the date being evaluated
-- This is important because it will change the final modifier
-- (adding days in some instances and subtracting days in others
IF @DSTSTOPMODIFIER > @DSTSTOPDAY
BEGIN
SET @DSTSTOPMODIFIER = 8 - @DSTSTOPMODIFIER
END
ELSE
BEGIN
SET @DSTSTOPMODIFIER = (@DSTSTOPMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTSTOPDT = DATEADD(day,(@DSTSTOPINSTANCE*7)-(8-@DSTSTOPDAY) + @DSTSTOPMODIFIER,@DSTSTOPDT)
SET @DSTSTOPDT = DATEADD(hour,@DSTSTOPHOUR,@DSTSTOPDT)
SET @DSTSTOPDT = DATEADD(minute,@DSTSTOPMINUTE,@DSTSTOPDT)
-- Check to see if the date being evaluated falls between the
-- DST start and stop date/times
IF @NEWDT BETWEEN @DSTSTARTDT AND @DSTSTOPDT
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
-- Return the new date that has been converted from UTC time
RETURN @NEWDT
END
GO
-- =============================================
-- Create scalar function (NEW_TIME)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NEW_TIME')
DROP FUNCTION NEW_TIME
GO
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
GO
Robert Ford
|
|
|
|
|
Previously I had fixed an issue with the GET_UTCTIME function but did not realize the same issue existed in the GET_TZTIME function. That issue was brought out joel_Charlebois. As I was looking into what he pointed out, I came time find out that there was another issues as well. The functions were not properly evaluating the hour that happened directly prior to when DST starts or stops. I fixed the issue that joel_charlebois as well as the other issue. Below is the new code.
-- *************************
--
-- SQL 2005 Time Zone Conversion setup tables and functions
--
-- Created by: Robert Ford
-- Created on: 11/17/2008
-- Modified on:01/21/2010
--
-- 11/17/2008
-- Creation Date
--
-- 11/30/2008
-- 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.
--
-- 06/11/2009
-- 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.
--
-- 01/21/2010
-- Fixed an issue with the GET_UTCTIME function in regards to time zones that are positive GMT adjusted.
--
-- 04/02/2010
-- Fixed an issue where the functions were not evaluating the hour prior to the conversion
-- time.
--
-- *************************
--
-- Tables:
-- TIME_ZONES
-- TIMEZONE_CD VARCHAR(6) NOT NULL - this value is used by the functions ot reference the other values
-- TIMEZONE_NAME VARCHAR(60) NOT NULL - name to describe the time zone code
-- OFFSET_HR INT NOT NULL - the offset hours for adjusting the date time
-- OFFSET_MI INT NOT NULL - the offset minutes for adjusting the date time
-- DST_OFFSET_HR INT NOT NULL - the day light offset hours for adjusting the date time
-- DST_OFFSET_MI INT NOT NULL - the day light offset minutes for adjusting the date time
-- DST_EFF_DT VARCHAR(10) NOT NULL - the day light savings effective date time formate: MMWDHHmm
-- DST_END_DT VARCHAR(10) NOT NULL - the day light savings end date time: MMWDHHmm
-- Notes: This table holds the time zone creterion used to convert date time values
-- DST EFF and END date format: MMWDHHmm
-- MM = two digit month (e.g. March = 3)
-- 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
--
-- Functions:
-- GET_UTCTIME
-- @DT DATETIME - LOCAL DATETIME
-- @TZ VARCHAR(12) - Time Zone code (must match a value in the database table)
-- Notes: Pass the time zone code and date and the function will
-- return a the date in UTC or GMT datetime
-- GET_TZTIME
-- @DT DATETIME - UTC or GMT DATETIME
-- @TZ VARCHAR(12) - Time Zone code (must match a value in the database table)
-- Notes: Pass the GMT or UTC date time and the time zone code
-- and the function will provide the date time adjusted for the specified
-- time zone.
--
-- NEW_TIME
-- @DT DATETIME - DATETIME to be converted
-- @TZ1 VARCHAR(12) - Time zone that the date time being passed is in
-- @TZ2 VARCHAR(12) - Time zone to convert the date time to
-- Note: Pass the date time, the time zone code that the date time is in,
-- and the time zone code that the date time should be converted to.
-- This function uses the GET_UTCTIME and GET_TZTIME 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.
--
--
-- 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].[TIME_ZONES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TIME_ZONES]
GO
-- Create the TIME_ZONES table
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
) ON [PRIMARY]
GO
-- Alter the table to add the primary key
ALTER TABLE [dbo].[TIME_ZONES] WITH NOCHECK ADD
CONSTRAINT [PK_TIME_ZONES] PRIMARY KEY CLUSTERED
(
[TIMEZONE_CD],
[EFF_DT]
) ON [PRIMARY]
GO
-- Add the field default contraints
ALTER TABLE [dbo].[TIME_ZONES] ADD
CONSTRAINT [DF_TIME_ZONES_OFFSET] DEFAULT ((-1)) FOR [OFFSET_HR],
CONSTRAINT [DF_TIME_ZONES_OFFSET_MI] DEFAULT (0) FOR [OFFSET_MI],
CONSTRAINT [DF_TIME_ZONES_DST_OFFSET] DEFAULT ((-1)) FOR [DST_OFFSET_HR],
CONSTRAINT [DF_TIME_ZONES_DST_OFFSET_MI] DEFAULT (0) FOR [DST_OFFSET_MI],
CONSTRAINT [DF_TIME_ZONES_DST_EFF_DT] DEFAULT ('03210200') FOR [DST_EFF_DT],
CONSTRAINT [DF_TIME_ZONES_DST_END_DT] DEFAULT ('11110200') FOR [DST_END_DT],
CONSTRAINT DF_TIME_ZONES_EFF_DT DEFAULT GETDATE() FOR [EFF_DT],
CONSTRAINT DF_TIME_ZONES_END_DT DEFAULT '12/31/9999' FOR [END_DT]
GO
-- Insert time zone information into the TIME_ZONES table
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('AKT','Alaskan Time',-9,0,-8,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('AT','Atlantic Time',-4,0,-3,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('AZ','Arizona Time',-7,0,-7,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('CT','Central Time',-6,0,-5,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('ET','Eastern Time',-5,0,-4,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('HT','Hawaii Time',-10,0,-10,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('MT','Mountain Time',-7,0,-6,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('NT','Newfoundland Time',-4,30,-2,30,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('PT','Pacific Time',-8,0,-7,0,'03210200','11110200')
GO
INSERT INTO time_zones(timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt)
VALUES('YT','Yukon Time',-8,0,-8,0,'03210200','11110200')
GO
-- =============================================
-- Create scalar function (GET_UTCTIME)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'GET_UTCTIME')
DROP FUNCTION GET_UTCTIME
GO
CREATE FUNCTION dbo.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)
DECLARE @DSTSTARTDT AS DATETIME
DECLARE @DSTSTOPDT AS DATETIME
-- 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
-- Increase the datetime by the hours and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,@OFFSETHR*-1,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI*-1,@NEWDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST start date
DECLARE @DSTSTARTMONTH INT
DECLARE @DSTSTARTINSTANCE INT
DECLARE @DSTSTARTDAY INT
DECLARE @DSTSTARTHOUR INT
DECLARE @DSTSTARTMINUTE INT
DECLARE @DSTSTARTMODIFIER INT
-- Perform calculations to determine date
SET @DSTSTARTMONTH = CONVERT(INT,SUBSTRING(@DSTEFFDT,1,2))-1
SET @DSTSTARTINSTANCE = CONVERT(INT,SUBSTRING(@DSTEFFDT,3,1))
SET @DSTSTARTDAY = CONVERT(INT,SUBSTRING(@DSTEFFDT,4,1))
SET @DSTSTARTHOUR = CONVERT(INT,SUBSTRING(@DSTEFFDT,5,2))
SET @DSTSTARTMINUTE = CONVERT(INT,SUBSTRING(@DSTEFFDT,7,2))
-- Set the start date variable to the first day of the year for the year of the date being evaluated
SET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Add month value for start pattern to the start date variable
SET @DSTSTARTDT = DATEADD(month,@DSTSTARTMONTH,@DSTSTARTDT)
-- Determine the modifier value needed to adjust the date when the date desired is in a
-- month that does not start on a Sunday
SET @DSTSTARTMODIFIER = DATEPART(weekday,@DSTSTARTDT)
-- Check to see if the modifier date is greater than the date being evaluated
-- This is important because it will change the final modifier
-- (adding days in some instances and subtracting days in others
IF @DSTSTARTMODIFIER > @DSTSTARTDAY
BEGIN
SET @DSTSTARTMODIFIER = 8 - @DSTSTARTMODIFIER
END
ELSE
BEGIN
SET @DSTSTARTMODIFIER = (@DSTSTARTMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTSTARTDT = DATEADD(day,(@DSTSTARTINSTANCE*7)-(8-@DSTSTARTDAY) + @DSTSTARTMODIFIER,@DSTSTARTDT)
SET @DSTSTARTDT = DATEADD(hour,@DSTSTARTHOUR,@DSTSTARTDT)
SET @DSTSTARTDT = DATEADD(minute,@DSTSTARTMINUTE,@DSTSTARTDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST stop date
DECLARE @DSTSTOPMONTH INT
DECLARE @DSTSTOPINSTANCE INT
DECLARE @DSTSTOPDAY INT
DECLARE @DSTSTOPHOUR INT
DECLARE @DSTSTOPMINUTE INT
DECLARE @DSTSTOPMODIFIER INT
-- Perform calculations to determine date
SET @DSTSTOPMONTH = CONVERT(INT,SUBSTRING(@DSTENDDT,1,2))-1
SET @DSTSTOPINSTANCE = CONVERT(INT,SUBSTRING(@DSTENDDT,3,1))
SET @DSTSTOPDAY = CONVERT(INT,SUBSTRING(@DSTENDDT,4,1))
SET @DSTSTOPHOUR = CONVERT(INT,SUBSTRING(@DSTENDDT,5,2))
SET @DSTSTOPMINUTE = CONVERT(INT,SUBSTRING(@DSTENDDT,7,2))
-- Set the start date variable to the first day of the year for the year of the date being evaluated
SET @DSTSTOPDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Determine the modifier value needed to adjust the date when the date desired is in a
SET @DSTSTOPDT = DATEADD(month,@DSTSTOPMONTH,@DSTSTOPDT)
-- Determine the modifier value needed to adjust the date when the date desired is in a
-- month that does not start on a Sunday
SET @DSTSTOPMODIFIER = DATEPART(weekday,@DSTSTOPDT)
-- Check to see if the modifier date is greater than the date being evaluated
-- This is important because it will change the final modifier
-- (adding days in some instances and subtracting days in others
IF @DSTSTOPMODIFIER > @DSTSTOPDAY
BEGIN
SET @DSTSTOPMODIFIER = 8 - @DSTSTOPMODIFIER
END
ELSE
BEGIN
SET @DSTSTOPMODIFIER = (@DSTSTOPMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTSTOPDT = DATEADD(day,(@DSTSTOPINSTANCE*7)-(8-@DSTSTOPDAY) + @DSTSTOPMODIFIER,@DSTSTOPDT)
SET @DSTSTOPDT = DATEADD(hour,@DSTSTOPHOUR,@DSTSTOPDT)
SET @DSTSTOPDT = DATEADD(minute,@DSTSTOPMINUTE,@DSTSTOPDT)
-- Check to see if the date being evaluated falls between the
-- DST start and stop date/times
IF @DT BETWEEN @DSTSTARTDT AND DATEADD(hour,-1,DATEADD(second,-1,@DSTSTOPDT))
BEGIN
SET @NEWDT = DATEADD(hh,@DSTOFFSETHR*-1,@DT)
SET @NEWDT = DATEADD(mi,@DSTOFFSETMI*-1,@NEWDT)
END
-- Return the new date that has been converted from UTC time
RETURN @NEWDT
END
GO
-- =============================================
-- Create scalar function (GET_TZTIME)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'GET_TZTIME')
DROP FUNCTION GET_TZTIME
GO
CREATE FUNCTION dbo.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)
DECLARE @DSTSTARTDT AS DATETIME
DECLARE @DSTSTOPDT AS DATETIME
-- 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
-- Increase the datetime by the hours and minutes assigned to the timezone
SET @NEWDT = DATEADD(hh,@OFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI,@NEWDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST start date
DECLARE @DSTSTARTMONTH INT
DECLARE @DSTSTARTINSTANCE INT
DECLARE @DSTSTARTDAY INT
DECLARE @DSTSTARTHOUR INT
DECLARE @DSTSTARTMINUTE INT
DECLARE @DSTSTARTMODIFIER INT
-- Perform calculations to determine date
SET @DSTSTARTMONTH = CONVERT(INT,SUBSTRING(@DSTEFFDT,1,2))-1
SET @DSTSTARTINSTANCE = CONVERT(INT,SUBSTRING(@DSTEFFDT,3,1))
SET @DSTSTARTDAY = CONVERT(INT,SUBSTRING(@DSTEFFDT,4,1))
SET @DSTSTARTHOUR = CONVERT(INT,SUBSTRING(@DSTEFFDT,5,2))
SET @DSTSTARTMINUTE = CONVERT(INT,SUBSTRING(@DSTEFFDT,7,2))
-- Set the start date variable to the first day of the year for the year of the date being evaluated
SET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Add month value for start pattern to the start date variable
SET @DSTSTARTDT = DATEADD(month,@DSTSTARTMONTH,@DSTSTARTDT)
-- Determine the modifier value needed to adjust the date when the date desired is in a
-- month that does not start on a Sunday
SET @DSTSTARTMODIFIER = DATEPART(weekday,@DSTSTARTDT)
-- Check to see if the modifier date is greater than the date being evaluated
-- This is important because it will change the final modifier
-- (adding days in some instances and subtracting days in others
IF @DSTSTARTMODIFIER > @DSTSTARTDAY
BEGIN
SET @DSTSTARTMODIFIER = 8 - @DSTSTARTMODIFIER
END
ELSE
BEGIN
SET @DSTSTARTMODIFIER = (@DSTSTARTMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTSTARTDT = DATEADD(day,(@DSTSTARTINSTANCE*7)-(8-@DSTSTARTDAY) + @DSTSTARTMODIFIER,@DSTSTARTDT)
SET @DSTSTARTDT = DATEADD(hour,@DSTSTARTHOUR,@DSTSTARTDT)
SET @DSTSTARTDT = DATEADD(minute,@DSTSTARTMINUTE,@DSTSTARTDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST stop date
DECLARE @DSTSTOPMONTH INT
DECLARE @DSTSTOPINSTANCE INT
DECLARE @DSTSTOPDAY INT
DECLARE @DSTSTOPHOUR INT
DECLARE @DSTSTOPMINUTE INT
DECLARE @DSTSTOPMODIFIER INT
-- Perform calculations to determine date
SET @DSTSTOPMONTH = CONVERT(INT,SUBSTRING(@DSTENDDT,1,2))-1
SET @DSTSTOPINSTANCE = CONVERT(INT,SUBSTRING(@DSTENDDT,3,1))
SET @DSTSTOPDAY = CONVERT(INT,SUBSTRING(@DSTENDDT,4,1))
SET @DSTSTOPHOUR = CONVERT(INT,SUBSTRING(@DSTENDDT,5,2))
SET @DSTSTOPMINUTE = CONVERT(INT,SUBSTRING(@DSTENDDT,7,2))
-- Set the start date variable to the first day of the year for the year of the date being evaluated
SET @DSTSTOPDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Determine the modifier value needed to adjust the date when the date desired is in a
SET @DSTSTOPDT = DATEADD(month,@DSTSTOPMONTH,@DSTSTOPDT)
-- Determine the modifier value needed to adjust the date when the date desired is in a
-- month that does not start on a Sunday
SET @DSTSTOPMODIFIER = DATEPART(weekday,@DSTSTOPDT)
-- Check to see if the modifier date is greater than the date being evaluated
-- This is important because it will change the final modifier
-- (adding days in some instances and subtracting days in others
IF @DSTSTOPMODIFIER > @DSTSTOPDAY
BEGIN
SET @DSTSTOPMODIFIER = 8 - @DSTSTOPMODIFIER
END
ELSE
BEGIN
SET @DSTSTOPMODIFIER = (@DSTSTOPMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTSTOPDT = DATEADD(day,(@DSTSTOPINSTANCE*7)-(8-@DSTSTOPDAY) + @DSTSTOPMODIFIER,@DSTSTOPDT)
SET @DSTSTOPDT = DATEADD(hour,@DSTSTOPHOUR,@DSTSTOPDT)
SET @DSTSTOPDT = DATEADD(minute,@DSTSTOPMINUTE,@DSTSTOPDT)
-- Check to see if the date being evaluated falls between the
-- DST start and stop date/times
IF @NEWDT BETWEEN @DSTSTARTDT AND DATEADD(hour,-1,DATEADD(second,-1,@DSTSTOPDT))
BEGIN
SET @NEWDT = DATEADD(hh,@DSTOFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,@DSTOFFSETMI,@NEWDT)
END
-- Return the new date that has been converted from UTC time
RETURN @NEWDT
END
GO
-- =============================================
-- Create scalar function (NEW_TIME)
-- =============================================
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'NEW_TIME')
DROP FUNCTION NEW_TIME
GO
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
GO
Robert Ford
|
|
|
|
|
Awesome code.
Cant seem to copy & paste it though, it unformats it completely.
Not sure if you saw this thread... (sorry I only just realised this code changes thread was here too)
"Suggested updates Colin 2 22:17 15 Apr '10
Hi Robert, thanks for this code, I have found it very useful so far. There were a couple of issues that I noticed though which I have attempted to resolve. I'd appreciate your evaluation.
Regards,
Colin"
Also... just curious if there's a reason for DST_EFF_DT & DST_END_DT (MMWDHHmm) fields instead of splitting them into multiple fields (month, wk, day, hh, mm).
Also not sure yet if code handles empty ('' as set to NOT NULL) DST_EFF_DT and DST_END_DT values for timezones that have no daylight savings.
I've started adding in the mutliple global timezones that we need to handle...
-- AZ USA TimeZones from Jul 1989
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('AZ','Arizona USA = GMT -7 = MST',-7,0,-7,0,'','','01 Jul 1989','30 Jun 9999')
-- NSW Australia TimeZones from Jul 1989
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','03110300','01 Jul 1989','30 Jun 1995')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','03L10300','01 Jul 1995','30 Jun 1999')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'08L10200','03L10300','01 Jul 1999','01 Sep 2000')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','03L10300','01 Jul 2000','30 Jun 2007')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','04110300','01 Jul 2007','30 Jun 2008')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10110200','04110300','01 Jul 2008','30 Jun 9999')
modified on Wednesday, July 21, 2010 10:00 AM
|
|
|
|
|
shell_l_d,
I just saw the post this morning and will reply to it after this one. The two fields that you reference are not date/time data types. They are varchar fields and they hold a value that you mentioned. They hold a string that represents the month, week, day of week and hour that DST starts or ends, (e.g. 03210200 represents third month, second week in that month, first day of that week and the time of 02:00 am). I took that approach instead of a date/time value because I did not want to have to mess with the year value. I did not split them into multiple fields because I only wanted to have to work with one value and to keep it simple (or at least simple for me). If that would make things easier for you then make the necessary changes. I do not think it would be difficult to take that approach. I have see other applications do something similar. It is one of those design decisions that is neither right or wrong just developer's preference.
The code will not handle null values in those fields. In fact I am not sure if the tables would allow it (without modification). If a timezone does not have a DST value then just set the OFFSET_HR and DST_OFFSET_HR values to be the same. Then it does not matter what the DST_EFF_DT and DST_END_DT values are (as long as DST_EFF_DT is smaller than DST_END_DT).
Regarding the copying and pasting I have learned a bit of a trick when copying from a web page to MS SQL Management Studio. In HTML when you want a new line you use the tag . There is no line feed or carriage return elements. When copying data from a web page to SQL Management Studio it tried to keep what you copied in the same format though it does not recognize the tag and such it makes it as one long string (except when there are other tags that it does recognize). The trick that I learned is to copy the info to Notepad first. For some reason notepad recognizes the tag as a NEW LINE and will keep the the data on the right lines. Then copy from notepad to SQL Management Studio. The code will be formatted in the manner that you want. This trick works in reverse when copying SQL statements from Management Studio to Outlook.
Robert Ford
|
|
|
|
|
Hi Robert, no prob, was just curious about the string.
Managed to get the code copied by copying to MS Word first, as formatting was lost when copying to Notepad or SQL Query Analyser (sql2000).
I've got both your v5 & 'Colin 2's edited version of it installed & was comparing the 2.
I'm having a problem with DST start & end dates at moment:
Fill table with few timezones (info obtained from timeandate.com):
-- AZ USA TimeZones from Jul 1989
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('AZ','Arizona USA = GMT -7 = MST',-7,0,-7,0,'11111111','11111111','01 Jul 1989','30 Jun 9999')
-- NSW Australia TimeZones from Jul 1989
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','03110300','01 Jul 1989','30 Jun 1995')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','03L10300','01 Jul 1995','30 Jun 1999')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'08L10200','03L10300','01 Jul 1999','01 Sep 2000')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','03L10300','01 Jul 2000','30 Jun 2007')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10L10200','04110300','01 Jul 2007','30 Jun 2008')
insert into time_zones (timezone_cd, timezone_name, offset_hr, offset_mi, dst_offset_hr, dst_offset_mi, dst_eff_dt, dst_end_dt, eff_dt, end_dt)
values ('NSW','New South Wales Australia = GMT +10 = AEST and AEDT',10,0,11,0,'10110200','04110300','01 Jul 2008','30 Jun 9999')
Run a comparison:
--21Nov2010 23:45 NSW
--21Nov2010 12:45 GMT
--21Nov2010 5:45 AZ
-- Both return Non DST time (wrong) of 21Nov2010 6:45, instead of 5:45 DST time
DECLARE @MyNewDT AS DATETIME
execute dbo.P_NEW_TIME '21 Nov 2010 23:45:00','NSW','AZ', @MyNewDT output
SELECT @MyNewDT
select dbo.new_time('21 Nov 2010 23:45:00','NSW','AZ')
--21Jul2010 23:45 NSW
--21Jul2010 13:45 GMT
--21Jul2010 6:45 AZ
-- Both return Non DST time (as expected).
execute dbo.P_NEW_TIME '21 Jul 2010 23:45:00','NSW','AZ', @MyNewDT output
SELECT @MyNewDT
select dbo.new_time('21 Jul 2010 23:45:00','NSW','AZ')
Here's the results in the messages window:
P_GET_UTCTIME 0: DT=21 Nov 2010 23:45:00
P_GET_UTCTIME 1: NEWDT=21 Nov 2010 13:45:00
P_GET_UTCTIME 2: DSTSTARTDT=03 Oct 2010 02:00:00
P_GET_UTCTIME 3: DSTSTOPDT=04 Apr 2010 03:00:00
P_NEW_TIME 1: NEWDT=21 Nov 2010 13:45:00
P_GET_TZTIME 1: NEWDT=21 Nov 2010 06:45:00
P_GET_TZTIME 2: DSTSTARTDT=07 Nov 2010 11:11:00
P_GET_TZTIME 3: DSTSTOPDT=07 Nov 2010 11:11:00
P_NEW_TIME 2: NEWDT=21 Nov 2010 06:45:00
(1 row(s) affected)
(1 row(s) affected)
P_GET_UTCTIME 0: DT=21 Jul 2010 23:45:00
P_GET_UTCTIME 1: NEWDT=21 Jul 2010 13:45:00
P_GET_UTCTIME 2: DSTSTARTDT=03 Oct 2010 02:00:00
P_GET_UTCTIME 3: DSTSTOPDT=04 Apr 2010 03:00:00
P_NEW_TIME 1: NEWDT=21 Jul 2010 13:45:00
P_GET_TZTIME 1: NEWDT=21 Jul 2010 06:45:00
P_GET_TZTIME 2: DSTSTARTDT=07 Nov 2010 11:11:00
P_GET_TZTIME 3: DSTSTOPDT=07 Nov 2010 11:11:00
P_NEW_TIME 2: NEWDT=21 Jul 2010 06:45:00
(1 row(s) affected)
(1 row(s) affected)
Code extract... just added the PRINT statements to 'Colin 2's version, as cant seem to add PRINT statements to functions. Notice the End Date for DST is in the same year instead of the next year. Is this something I'm doing wrong or a possible bug please?
CREATE PROCEDURE [dbo].[P_GET_UTCTIME]
(@DT AS DATETIME,
@TZ AS VARCHAR(12),
@NEWDT DATETIME OUTPUT)
AS
BEGIN
-----------------------------------------------------------------------------
-- @DT DATETIME - LOCAL DATETIME
-- @TZ VARCHAR(12) - Time Zone code (must match a value in the database table)
-- @NEWDT DATETIME OUTPUT - the date in UTC or GMT datetime
-----------------------------------------------------------------------------
--Use of datepart in this and child procs, called functions assume that the first day of the week is a Sunday
SET DATEFIRST 7
--Declare variables
--DECLARE @year_of_date_for_conversion AS INT
DECLARE @OFFSETHR AS INT
DECLARE @OFFSETMI AS INT
DECLARE @DSTOFFSETHR AS INT
DECLARE @DSTOFFSETMI AS INT
DECLARE @DSTSTARTDT AS DATETIME
DECLARE @DSTSTOPDT AS DATETIME
-- 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
FROM time_zones
WHERE timezone_cd = @TZ AND
@DT BETWEEN eff_dt AND end_dt
-- Increase the datetime by the hours and minutes assigned to the timezone
-- As the procedure converts to UTC, the offset in the timezone table need to be reversed as they assume
-- a conversion from GMT.
PRINT 'P_GET_UTCTIME 0: DT=' + CONVERT(VARCHAR(20), @DT, 113 )
SET @NEWDT = DATEADD(hh,@OFFSETHR*-1,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI*-1,@NEWDT)
PRINT 'P_GET_UTCTIME 1: NEWDT=' + CONVERT(VARCHAR(20), @NEWDT, 113 )
-- select 'Start or end Date' = calculate_daylight_savings_dates('time_zone','Start / End', 'date being converted')
-- complete datetime is required to cater for changes in DST
execute calculate_daylight_savings @TZ,'Start',@DT, @DSTSTARTDT OUTPUT
execute calculate_daylight_savings @TZ,'End',@DT, @DSTSTOPDT OUTPUT
PRINT 'P_GET_UTCTIME 2: DSTSTARTDT=' + CONVERT(VARCHAR(20), @DSTSTARTDT, 113 )
PRINT 'P_GET_UTCTIME 3: DSTSTOPDT=' + CONVERT(VARCHAR(20), @DSTSTOPDT, 113 )
-- Check to see if the date being evaluated falls between the
-- DST start and stop date/times
IF @DT BETWEEN @DSTSTARTDT AND DATEADD(hour,-1,DATEADD(second,-1,@DSTSTOPDT))
BEGIN
SET @NEWDT = DATEADD(hh,@DSTOFFSETHR*-1,@DT)
SET @NEWDT = DATEADD(mi,@DSTOFFSETMI*-1,@NEWDT)
PRINT 'P_GET_UTCTIME 4: NEWDT=' + CONVERT(VARCHAR(20), @NEWDT, 113 )
END
END
GO
Thanks so much
modified on Wednesday, July 21, 2010 10:05 PM
|
|
|
|
|
Here's a fix for the DST ends in the following year scenario (eg: Australia):
Extract from 'Colin 2' version of calculate_daylight_savings_dates:
I added the @DSTENDS_NXT_YR flag...
DECLARE @DSTENDS_NXT_YR INT
--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
.... etc ....
@DSTENDS_NXT_YR=case when CONVERT(INT,SUBSTRING(dst_eff_dt,1,2)) > CONVERT(INT,SUBSTRING(dst_end_dt,1,2)) then 1 else 0 end
FROM time_zones
.... etc ....
-- 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 @DSTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@date_being_converted)))
-- Handle scenario where DST ends in the next year (eg: AUSTRALIA)
IF @dst_start_end_flag='End' AND @DSTENDS_NXT_YR = 1
SET @DSTDT = DATEADD(year,1,@DSTDT)
|
|
|
|
|
Ok, hope you dont mind but I've made some changes & here's the code
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:
-- Created on: 17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2' per http:
-- Edited by 'shell_l_d' per http:
-----------------------------------------------------------------------------------
--
-- 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:
-- 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:
-- 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:
-- Convert PROCEDURES (Colin 2) back to FUNCTIONS (RobertFord), so can use them in a select statement.
--
-- 29Jul2010
-- Edited by 'shell_l_d' per http:
-- 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:
-- Created on: 17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2' per http:
-- Edited by 'shell_l_d' per http:
-----------------------------------------------------------------------------------
-- @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:
-- Created on: 17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2' per http:
-- Edited by 'shell_l_d' per http:
-----------------------------------------------------------------------------------
-- @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:
-- Created on: 17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2' per http:
-- Edited by 'shell_l_d' per http:
-----------------------------------------------------------------------------------
-- @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:
-- Created on: 17Nov2008
-- Modified on: 29Jul2010
-- Edited by 'Colin 2' per http:
-- Edited by 'shell_l_d' per http:
-----------------------------------------------------------------------------------
-- @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)
--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
|
|
|
|
|
Great article and thanks for the code. One problem...the formatting of the code is such that it's a nightmare to paste into any editor. Can you repost source as an attachment?
Thanks
Callon
|
|
|
|
|
Robert was unable to edit the main page after a certain time, hence he created this Code Changes thread. Unfortunately cant seem to attach a file in the threads though.
I too had trouble copying & pasting the code to edit it myself... found it worked if copied & pasted to either WordPad/Word Doc then to SQL Server, not to Notepad then SQL Server (Query Analyser):
http://www.codeproject.com/Questions/95498/Newbie-Q-How-to-Copy-Paste-Code-Block-without-loos.aspx[^]
P.S. I just added a minor update to the code, so it handles NULL parameters passed.
modified on Thursday, August 5, 2010 4:29 AM
|
|
|
|
|
Callon,
If you are still having issues I can email it to you directly (my email address is visible in my profile). shell_l_d made some changes that you may be interested in. If you would like her changes then you would need to contact her. I have not reviewed them yet to see if they fit the needs of what I am using the code for.
Robert Ford
|
|
|
|
|
New to this site. I am in need to convert a huge set of historical UTC datetime into local time and came across this site. I believe that this is exactly what I need in my SQL server. Thank you for all the great work.
Is there a version control on this code? Where can I find the latest revision? I noticed the attched source code was dated 11/30/2008. I found quite a few revision happend after that date.
While I went through the source code in the attachment, I believed that there is a flaw in the GET_TZTIME function.
Based on the time zone example provided in home post, I believe the [DST_EFF_DT] and [DST_END_DT] are in local time ('03210200' = March second Sunday 2:00AM = US/Central Local Time ,'11110200' = November first Sunday 2:00AM = US/Central Local Time ). However the input date field for GET_TZTIME is in UTC time, which needs to be converted into local time to compare with the [DST_EFF_DT] and [DST_END_DT] or the the [DST_EFF_DT] and [DST_END_DT] should be converted in to UTC time to compare with the UTC time input. Otherwise the result won't be rigth when the input time falls into the cutoff range.
I haven't gone throught all the revisions get. I am not sure whether this issue get resolved in the later revisions. Could anyone please send me the latest revision?
Thanks,
|
|
|
|
|
* The latest version of the [modified] code is here:
http://www.codeproject.com/Articles/31146/SQL-2005-Time-Zone-Conversion-Functions?msg=3542594#xx3542594xx
Re: Time Zone Function sv5 [modified] 6:09 22Jul2010
Code as at 29Jul2010 (was edited).
* Copy & paste code to either WordPad/WordDoc then to SQL Server
(not Notepad then SQL Server Query Analyser).
|
|
|
|
|
shell_l_d,
I have no problem with anyone making changes to the code. I know that I do not know everything and I am sure that there are enhancements that can be made to make the code more efficient and functional. The purpose was to create a starting point for others to use to fill a hole that currently exists in SQL server. This is why I make the license open source. Make changes to fit your needs, share for others and have fun.
Robert Ford
|
|
|
|
|
Great stuff!
Few questions,
1) Where does one go to get the "Data" to add to the "Insert Into TimeZones Table"? I am in need of just about ALL TZ (data). Is there a site I can reference, or does someone already have this?
TZ - Bias (in use at my location)
Arab Standard Time 180
Arabian Standard Time 240
Argentina Standard Time -180
Armenian Standard Time 240
Atlantic Standard Time -240
AUS Eastern Standard Time 600
Cen. Australia Standard Time 570
Central America Standard Time -360
Central Europe Standard Time 60
Central European Standard Time 60
Central Pacific Standard Time 660
Central Standard Time -360
Central Standard Time (Mexico) -360
China Standard Time 480
E. Australia Standard Time 600
E. South America Standard Time -180
Eastern Standard Time -300
Egypt Standard Time 120
FLE Standard Time 120
GMT Standard Time 0
Greenwich Standard Time 0
GTB Standard Time 120
Hawaiian Standard Time -600
Hora est. de América Central -360
Hora est. del Pacífico de SA -300
Hora estándar central (México) -360
Hora estándar de México -360
Hora estándar GMT 0
Hora estándar romance 60
India Standard Time 330
Jerusalem Standard Time 120
Jordan Standard Time 120
Korea Standard Time 540
Malay Peninsula Standard Time 480
Mexico Standard Time -360
Middle East Standard Time 120
Montevideo Standard Time -180
Mountain Standard Time -420
Mountain Standard Time (Mexico) -420
Namibia Standard Time 120
New Zealand Standard Time 720
ora solare Europa occidentale 60
Öйú±ê׼ʱ¼ä 480
Pacific SA Standard Time -240
Pacific Standard Time -480
Romance Standard Time 60
Russian Standard Time 180
SA Eastern Standard Time -180
SA Pacific Standard Time -300
SA Western Standard Time -240
SE Asia Standard Time 420
South Africa Standard Time 120
Taipei Standard Time 480
Tokyo Standard Time 540
US Eastern Standard Time -300
US Mountain Standard Time -420
UTC-11 -660
Venezuela Standard Time -270
W. Australia Standard Time 480
W. Europe Standard Time 60
2) All the date/time stamps are imported into one central DB as TZ from source location. I guess the good news is I need them all changed into EST only if the source data is not already in EST.
The following is what I have to work with; that I can feed to the function/SP:
Bias - TZ Name - TZ GMT/caption - Date/time
-480 Pacific Standard Time (GMT-08:00) Pacific Time (US & Canada) 6/10/2009 9:37:22 AM
-420 Mountain Standard Time (GMT-07:00) Mountain Time (US & Canada) 6/10/2009 6:37:22 AM
-360 Central Standard Time (GMT-06:00) Central Time (US & Canada) 6/10/2009 7:37:22 AM
-360 Mexico Standard Time (GMT-06:00) Guadalajara, Mexico City, Monterrey - Old 6/10/2009 7:37:22 AM
-300 Eastern Standard Time (GMT-05:00) Eastern Time (US & Canada) 11/30/2005 3:06:26 PM
-180 Argentina Standard Time (GMT-03:00) Buenos Aires 5/3/2010 10:59:12 PM
-180 E. South America Standard Time (GMT-03:00) Brasilia 6/10/2009 9:37:22 AM
Will this work?
|
|
|
|
|
* The latest version of the [modified] code is here:
http://www.codeproject.com/Articles/31146/SQL-2005-Time-Zone-Conversion-Functions?msg=3542594#xx3542594xx
Re: Time Zone Function sv5 [modified] 6:09 22Jul2010
Code as at 29Jul2010 (was edited).
* Copy & paste code to either WordPad/WordDoc then to SQL Server
(not Notepad then SQL Server Query Analyser).
|
|
|
|
|
Seems like this implies there are only positive offsets when converting from a TZ time to a UTC time but GET_TZTIME respects the sign of the offset. Never mind - I see it is fixed in the downloadable code.
|
|
|
|
|
Some time zones (i.e. Mexico) are defined as ending on the LAST Sunday of a month which can be the 4th or 5th depending on month and year - there does not seem to be a way to use this method here. I have written a function that will translate the MMWDHHmm enpoints to actual datetimes given the dt they are going to be used with and that function will handle a 5 for week number meaning the last one in that month which could be 4 or 5. It also lets you change the function that does the comparisons to use dates which solves the previous problem I posted if it is a problem.
|
|
|
|
|
Tom,
Again, thanks for pointing out something that is missing. Due to my unfamiliarity of timezones outside of the US this did not occur to me. Would you mind providing more details of the changes that you made due to these missing options so that others may be able to benefit from it as well?
Robert Ford
|
|
|
|
|
I am concerned that I am missing something or that there is a problem with your posting (and possibly with the code you are using at work) that you may want to correct. I may be missing something but the logic seems to be:
Record the start and end dates of DST for a TimeZone as MMWDHHmm. Convert the DateTime in question to the same format and see if it is between the DST start and end dates. If - yes apply DST offset else apply regular offset.
I have copied the code and tried it and there are many datetimes that it fails to convert correctly. For example: 11/06/2010 is the first Saturday in November and it is before the first Sunday in November (11/07/2010) therefore it should be in DST. The code, however, says that 1117HHmm (first Saturday in November) is not between 0321HHmm and 1111HHmm. It seems that every first Monday - Saturday in November will be treated as after the first Sunday regardless of what year it is. Am I missing something or have I implemented the code incorrectly?
|
|
|
|
|
Tom,
Thanks for your response. You are correct that there is a bug in the code. For those who read this and are not sure what this is about I will explain. The way the code for the function GET_TZTIME the evaluation on whether a date is within the DST range is not correct when the DST stop date is in a month that does not start on a Sunday. I did not initially notice this because this year November starts on a Sunday so I did not get any errors and everything was being evaluated correct.
Tonight I took some time to rework that function to resolve this issue. I have posted the changes to the code in a new thread to keep track of the code changes.
Thanks for taking the time to point out the issue so that I could get it resolved.
Robert Ford
|
|
|
|
|
i don't know , how to pass last two parameters in this function .
select dbo.NEW_TIME(getdate(),,)
|
|
|
|
|
Trushitshah, The second two variables are the timezone_cd values that you want to change the date/time from and to. The timezone_cd values can be retrieved from the timezones table in the database. If you ran the SQL script in the article then and example would be if you are converting from central US time to arizona US time then the first value would be CT and the second would be AZ (e.g. SELECT dbo.new_time(GETDATE(),'CT','AZ')). If you have any other issues please let me now.
Robert Ford
|
|
|
|
|
Hi Robert, Very good article. How would you manage a change in daylight savings for a particular year. In AUS we are regularly changing DST start and end dates for particular years. Could you add it to your code field so that there may be multiple rows in the table for a particular timezone?
|
|
|
|
|
Stribbed,
Thanks for the feedback. I had not realized that there were areas where the daylight time changes on a regular basis. I modified the table and functions so that the admin can have multiple entries for the timezone code with different effective and end dates so that there is not any interuption on their funcitonality. The effective and end dates is compared to the date provided to see if it falls withing the applicable range for that criteria. Now I did not add any triggers or anything to make that there are not any over lapping ranges, that is the only danger that I can see in my initial testing, though that can be done (though typically these types of control logic are handled in an application and not the database code). Please let me know if you have any other request, questions or problems.
Thanks,
Robert Ford
robertford@ford-fam.com
Robert Ford
|
|
|
|
|
Only logical issue with this code is, it is switching the daylight to standard time on basis of GMT. But it should be based on local time.
|
|
|
|
|