|
Hello Robert,
Thanks for this great article.
I am confused getting the bits together since there have been some code changes.
Can you put the whole SQL script in single reply. I cant see the table for the DS effective Date abd End Date which should be linking to the time zone table as well.
Thanks Again,
Imran
|
|
|
|
|
Imran,
I am sorry for the not so timely reply. Here is the full script.
<pre>-- *************************
--
-- SQL 2005 Time Zone Conversion setup tables and functions
--
-- Created by: Robert Ford
-- Created on: 11/17/2008
-- Modified on: 11/30/2008
--
-- 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.
--
-- *************************
--
-- 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,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
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</pre>
Robert Ford
|
|
|
|
|
Thanks Robert,
Great Stuff ...
Have a question unrelated to the logic. Do you have an idea where can we get the list of the Daylight saving data information for different timezones. Is it something which I will need to compile or are there any lists available. i did some survey but looks like we can only buy them for about $3000.
Any feedback would be appreciated.
Thanks again ... brilliant piece of work.
Imran
|
|
|
|
|
Imran,
The answer to this depends upon how you want it. If you just want the dst adjustment for the basic time zone then it is available on quite a few websites though you might have to do a little work getting it off the web page and onto something else. A little trick I use is with Excel. If the web page has a table on it and you have Excel 2003 or greater then there is a neat feature where you can right click on the table and select the option "Export to Excel" and it will download the table to Excel. This has saved me a lot of time for many different reasons. Some websites that you may be interested in are as follows; http://en.wikipedia.org/wiki/List_of_time_zone_abbreviations, http://en.wikipedia.org/wiki/List_of_time_zones, http://en.wikipedia.org/wiki/List_of_zoneinfo_time_zones. Now if you are in need of regional info or timezone by a region code, postal code, or zip code that gets a little tricky. The info is out there though I have not been able to find it downloadable for free. You would have to do some work moving it from a website to you own media.
I hope that this helps. Let me know if you have any other questions.
Robert Ford
|
|
|
|
|
Hi Robert, it it correct to use ABS in GET_UTCTIME. I am based in India with an offset of +05:30 from UTC.
Using your function to convert:
25 Dec, 2005 12:00:00
to UTC results in:
25 Dec, 2005 05:30:00
whereas it should have been:
24 Dec, 2005 06:30:00
Proabably, you should have used:
SET @NEWDT = DATEADD(hh,-1 * @OFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,-1 * @OFFSETMI,@NEWDT)
|
|
|
|
|
Rahul,
Thank you for your note. I am sorry I have not responded sooner (my laptop was getting fixed). I a bit confused. Based upon what you mentioned it sounds like you expected the converted time to be prior to UTC time. If your desired time is +5:30 UTC and UTC is 12:00 am then the desired time should be 5:30 ahead which is 5:30 am. You had mentioned that you expected the desired time to be 6:30 pm which is -5:30 UTC adjustment.
If I am misunderstanding please let me know so that I can see if there is an adjustment to the code that needs to be made.
Robert Ford
|
|
|
|
|
Hi robert,
the function I am talking about is GET_UTCTIME, which converts local time to UTC Time. So, if I supply it 15 Jan, 2010 12:00 a.m., and tell it that the local time is in Indian time (+05:30), then the Utc time corresponding to it is 14 Jan, 2010 06:30 p.m.
Isn't it?? Your function gives: 15 Jan, 2010 06:30 a.m. Rememeber, if I am in India, I am ahead of Utc time. So, my local time converted to Utc time should be behind me.
|
|
|
|
|
Rahul,
Thank you for clarifying I understand what you mean. You are correct there was a slight error in the code. I do not deal with time zones that are "+" GMT very often and I missed something. You were correct in that adding a "* -1" is the best approach to fixing the issue. I am going to update the code in the thread that I started for those changes in the article. Thank your for letting me know that there was an issue.
Robert Ford
|
|
|
|
|
I think I found a bug.
Please try the following script:
BEGIN
DECLARE @Date AS datetime
DECLARE @TZ AS VARCHAR(12)
SET @Date = '2010-03-09 00:00:00'
SET @TZ = 'CT'
SET @Date = dbo.GET_UTCTIME(@Date, @TZ)
print 'UTC: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_TZTIME(@Date, @TZ)
print 'CT: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_UTCTIME(@Date, @TZ)
print 'UTC2: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_TZTIME(@Date, @TZ)
print 'CT2: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_UTCTIME(@Date, @TZ)
print 'UTC3: '+CONVERT(VARCHAR(20),@Date)
END
The output is:
UTC: Mar 9 2010 5:00AM
CT: Mar 8 2010 11:00PM
UTC2: Mar 9 2010 4:00AM
CT2: Mar 8 2010 10:00PM
UTC3: Mar 9 2010 3:00AM
As you can see, converting from CT to UTC and back to CT does not yield the same date times. It's an interesting bug as it seems to only occur between March 9th and March 13th for 2010.
I haven't had a chance to investigate further.
|
|
|
|
|
joel_charlebois,
Thanks for pointing out the issue. I am sorry it has taken me so long to respond. I researched the issue you pointed it out and feel a bit silly. The same issue existed in the GET_UTCTIME function, which was pointed out by someone else, and I did not think to see if it existed withi this one when I fixed that one.
I also discovered that there was another bug, where the time did not properly evaluate the hour before the start and stop of DST. I posted the new code under the "code changes" section of this message with the message "Time Zone Functions v5". After the changes the results to the test you performed are as below.
BEGIN
DECLARE @Date AS datetime
DECLARE @TZ AS VARCHAR(12)
SET @Date = '2010-03-09 00:00:00'
SET @TZ = 'CT'
SET @Date = dbo.GET_UTCTIME(@Date, @TZ)
print 'UTC: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_TZTIME(@Date, @TZ)
print 'CT: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_UTCTIME(@Date, @TZ)
print 'UTC2: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_TZTIME(@Date, @TZ)
print 'CT2: '+CONVERT(VARCHAR(20),@Date)
SET @Date = dbo.GET_UTCTIME(@Date, @TZ)
print 'UTC3: '+CONVERT(VARCHAR(20),@Date)
END
UTC: Mar 9 2010 6:00AM
CT: Mar 9 2010 12:00AM
UTC2: Mar 9 2010 6:00AM
CT2: Mar 9 2010 12:00AM
UTC3: Mar 9 2010 6:00AM
Please let me know if you find anything else that I missed.
Robert Ford
|
|
|
|
|