|
|
Precisely the workaround for MS SQL 2005 I needed. Really useful.
Thanks Robert
|
|
|
|
|
|
I love your project.
I think it is great. I did find an unusual issue for this year.
I have a record that I UTC date time stamped to be 2014-03-08 01:18:00.000.
I am in Central time and this returned March 7, 8:18 which is five hours off when it should have still been six hours off.
I looked at the code and it does calculate the record to be in daylight savings time when it is on the 8th (eventhough that is Saturday and not Sunday)
I know this is an old project but do you have a solution?
Again I love the project, I just found one issue for this year only.
Best Regards,
Mark Kilroy
|
|
|
|
|
It's very helpful!
But I also need data about timezones for Europe (0,+1,..) for dbo.TIME_ZONES table
Could anybody help me with it??
Thanks
|
|
|
|
|
|
So here I am trying to figure out which code is better. This one or Chris Tillotson article from July 2006 titled "SQL Server - Convert UTC to Local Time". They appear so similar. If they are so similar, how come you chose to duplicate a functionality rather than improve and make suggestions to his code? If not, how is yours better/different?
-Andy
|
|
|
|
|
Andy,
To be honest I had not come across that article prior to you presenting it to me. It is interesting and parts of the code is similar to what I published. The biggest difference between the two is that how you intereact with the functions is a little different and that the older article does not have any functions for converting time from local to UTC or from one time zone to another time zone.
One of the reasons why I decided to write my own processes was becuase I did not have a lot of time and I had to put something out quickly (not the article, the solution). There are not a lot out there for SQL server on how to perform these types of features (or at least I could not find them at the time). Also I needed to have a solution that worked similar to the functions that exist in Oracle since we were converting progamming from Oracle to SQL and wanted to keep as mush of it as intact as possible (plus I am use to working with those functions so it was an easy transition for me).
My code is not any better or worse than what Andy published it just approaches the same issue a little differently and I have a couple of additional functions for additional features.
Robert Ford
|
|
|
|
|
* The latest version of the [modified] code of Robert's 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).
|
|
|
|
|
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
1.
The use of the datepart function assumes that the first day of the week is a Sunday. The default for my Sql Server instance assumes the first day of the week is a Monday.
set datefirst 1
select @@datefirst
--Converting Nov 1 11:30am GMT to ET (New York) returns 7.30am which is incorrect. The correct result is 6.30am
select dbo.new_time('1 Nov 2009 11:30:00','GMT','ET')
select dbo.new_time('2 Nov 2009 11:30:00','GMT','ET')
set datefirst 7
select dbo.new_time('1 Nov 2009 11:30:00','GMT','ET')
select dbo.new_time('2 Nov 2009 11:30:00','GMT','ET')
select @@datefirst
I don't want users to SET DATEFIRST prior to using the function, but unfortunately this statement cannot be used in a function, so I have converted the functions to stored procedures - code is at the end of this post.
2.
The DST code format (MMWDHHMM) assumes that daylight savings will start and end on the 1st, 2nd, 3rd or 4th occurence of a given day in the month (usually Sunday). Central European Time (and other European time zones) start and end on the last Sunday of March and October - in some years this will be the 4th Sunday and other years it is the fifth Sunday. I tested both variations and came up with problems for each format in different years:
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 ('TCET1','Test Central European Time 1',1,0,2,0,'03410100','10410100','1 Jan 1900 00:00:00','12 Dec 9999 00:00:00')
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 ('TCET2','Test Central European Time 2',1,0,2,0,'03510100','10510100','1 Jan 1900 00:00:00','12 Dec 9999 00:00:00')
set datefirst 7
--In 2010 using code 10/4/1/0100 gives the wrong time because the function
--calculates DST as starting on the 24th October, rather than the 31st.
select dbo.new_time('23 Oct 2010 11:30:00','GMT','TCET1')
select dbo.new_time('24 Oct 2010 11:30:00','GMT','TCET1')
select dbo.new_time('31 Oct 2010 11:30:00','GMT','TCET1')
--In 2009 using code 10/5/1/0100 gives the wrong time because October
--only has four Sundays - the function calculates DST as starting on Nov 1st.
select dbo.new_time('24 Oct 2009 11:30:00','GMT','TCET2')
select dbo.new_time('25 Oct 2009 11:30:00','GMT','TCET2')
select dbo.new_time('1 NOV 2009 11:30:00','GMT','TCET2')
I adapted the code in the new stored procedures to accept 'L' as an indicator of the occurence of daylight savings change day in a month. If 'L' is not present the character is cast to an int and the original code executed. The updated procs are below. Code to calculate DST has been split out into a separate stored proc calling a function.
CREATE PROCEDURE [dbo].[P_NEW_TIME]
(@DT AS DATETIME,
@TZ1 AS VARCHAR(12),
@TZ2 AS VARCHAR(12),
@NEWDT AS DATETIME OUTPUT)
AS
BEGIN
-- Declare variables
-- 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
execute P_GET_UTCTIME @DT, @TZ1, @NEWDT output
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
execute P_GET_TZTIME @NEWDT, @TZ2, @NEWDT output
END
END
CREATE PROCEDURE [dbo].[P_GET_UTCTIME]
(@DT AS DATETIME,
@TZ AS VARCHAR(12),
@NEWDT DATETIME OUTPUT)
AS
BEGIN
--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 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.
SET @NEWDT = DATEADD(hh,@OFFSETHR*-1,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI*-1,@NEWDT)
-- 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
-- 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
END
GO
CREATE PROCEDURE [dbo].[P_GET_TZTIME]
(@DT AS DATETIME,
@TZ AS VARCHAR(12),
@NEWDT DATETIME OUTPUT)
AS
BEGIN
-- DECLARE VARIABLES
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 from UTC, the offset in the timezone table can be used directly.
-- (When converting to UTC in dbo.GET_UTCTIME, the offset values need to be reversed).
SET @NEWDT = DATEADD(hh,@OFFSETHR,@DT)
SET @NEWDT = DATEADD(mi,@OFFSETMI,@NEWDT)
-- 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
-- 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
END
GO
CREATE PROCEDURE [dbo].[Calculate_Daylight_Savings] @timezone varchar(6),
@DST_START_END_FLAG varchar(5), @TIME_BEING_CONVERTED datetime,
@Daylight_Savings Datetime output
AS
--Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6, Sunday = 7
--The function that actually calculates the date assumes that the first day of the week
--is Sunday.
SET DATEFIRST 7
select @Daylight_Savings = dbo.calculate_daylight_savings_dates
(@timezone, @DST_START_END_FLAG, @TIME_BEING_CONVERTED)
GO
CREATE FUNCTION [dbo].[calculate_daylight_savings_dates]
(@timezone AS varchar(10),
@dst_start_end_flag AS VARCHAR(5),
@date_being_converted datetime)
RETURNS DATETIME
AS
BEGIN
--The function is constructing a datetime from a code because daylight savings time (DST) starts/ends on a different
--date every year (e.g. last Sunday in March is the start of DST in the Central European Timezone). The function begins
--by constructing the fist day of the year, determing how many months to add, using this with a datetime function to update
--the first day of the year to the first day of the month that DST changes. Finally, it determines the correct day. There
--are two algorithms for this - one for instances on which DST changes on the last occurence of a given day of the month,
--the other for instances where DST changes on the 1st - 4th occurrence of a given day of the month.
-- Script assumes Sunday is the first day of the week
-- execute 'set datefirst 7' prior to using function if Sunday is not the default first day of the week on the instance
-- of sql server the function is running on. The function is also called from stored procedure Calculate_Daylight_Savings
-- which sets datefirst correctly.
DECLARE @DSTOFFSETHR AS INT
DECLARE @DSTOFFSETMI AS INT
DECLARE @DST_DATETIME AS DATETIME
declare @Days_in_month as int
DECLARE @DST_MONTH_INCREMENT INT
DECLARE @DST_CHANGE_DAY int
DECLARE @DST_CHANGE_DAY_OCCURRENCE VARCHAR(1)
DECLARE @DST_CHANGE_DAY_OCCURRENCE_INT int
-- Declare variables needed to convert MMWDHHmm pattern to for DST start date
DECLARE @DSTDT DATETIME --Holding variable for constructing the date.
DECLARE @DSTHOUR INT
DECLARE @DSTMINUTE INT
DECLARE @DSTMODIFIER 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
@DSTOFFSETHR=dst_offset_hr,
@DSTOFFSETMI=dst_offset_mi,
@DST_MONTH_INCREMENT=case when @dst_start_end_flag='Start' then
CONVERT(INT,SUBSTRING(dst_eff_dt,1,2))-1
when @dst_start_end_flag='End' then
CONVERT(INT,SUBSTRING(dst_end_dt,1,2))-1
end,
@DST_CHANGE_DAY_OCCURRENCE=case when @dst_start_end_flag='Start' then
SUBSTRING(dst_eff_dt,3,1)
when @dst_start_end_flag='End' then
SUBSTRING(dst_end_dt,3,1)
end,
@DST_CHANGE_DAY=case when @dst_start_end_flag='Start' then
SUBSTRING(dst_eff_dt,4,1)
when @dst_start_end_flag='End' then
SUBSTRING(dst_end_dt,4,1)
end,
@DSTHOUR=case when @dst_start_end_flag='Start' then
CONVERT(INT,SUBSTRING(dst_eff_dt,5,2))
when @dst_start_end_flag='End' then
CONVERT(INT,SUBSTRING(dst_end_dt,5,2))
end,
@DSTMINUTE=case when @dst_start_end_flag='Start' then
CONVERT(INT,SUBSTRING(dst_eff_dt,7,2))
when @dst_start_end_flag='End' then
CONVERT(INT,SUBSTRING(dst_end_dt,7,2))
end
FROM time_zones
WHERE timezone_cd = @timezone AND
@date_being_converted BETWEEN eff_dt AND end_dt
--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 @DST_CHANGE_DAY_OCCURRENCE <> 'L'
SET @DST_CHANGE_DAY_OCCURRENCE_INT = CONVERT(INT,@DST_CHANGE_DAY_OCCURRENCE)
-- 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)))
-- Add month value for DST pattern to the holding date variable
SET @DSTDT = DATEADD(month,@DST_MONTH_INCREMENT,@DSTDT)
IF @DST_CHANGE_DAY_OCCURRENCE = 'L'
BEGIN
--Some of the days of the week occur five times in any given month. If the month has 31 days, the 1st, 2nd &
--3rd days will occur five times; 30 days - 1st and 2nd occur five times; 29th - 1st occurs 5 times.
--The case statement takes this pattern into account to calculate the last occurence of a given day in a month
--Number of days in month is: construct the first of the 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 @Days_in_month = convert(int,DATENAME(DAY,DATEADD(DAY,-1,DATEADD(Month,1,@DSTDT))))
if @Days_in_month = 31
begin
--if the 1st of the month is the dst_change_day, the dst_change_date is the 29th;
--if the 2nd of the month is the dst_change_day, the dst_change_date is the 30th;
--if the 3rd of the month is the dst_change_day, the dst_change_date is the 31st
--From the 4th onwards, dst_change_date is 20 days after the 1st dst_change_day date
if datepart(weekday,@DSTDT) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,28,@DSTDT)
else
if datepart(weekday,dateadd(day, 1, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,29,@DSTDT)
else
if datepart(weekday,dateadd(day, 2, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,30,@DSTDT)
else
if datepart(weekday,dateadd(day, 3, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,3,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 4, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,4,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 5, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,5,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 6, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,6,@DSTDT))),@DSTDT)
end
if @Days_in_month = 30
begin
--if the 1st of the month is the dst_change_day, the dst_change_date is the 29th;
--if the 2nd of the month is the dst_change_day, the dst_change_date is the 30th
--From the 3rd onwards, dst_change_date is 21 days from the 1st dst_change_day
if datepart(weekday,@DSTDT) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,28,@DSTDT)
else
if datepart(weekday,dateadd(day, 1, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,29,@DSTDT)
else
if datepart(weekday,dateadd(day, 2, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,2,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 3, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,3,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 4, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,4,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 5, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,5,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 6, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,6,@DSTDT))),@DSTDT)
end
if @Days_in_month = 29
begin
--if the 1st of the month is the dst_change_day, the dst_change_date is the 29th - applicable in February 2032 if required...
--From the 2nd onwards, dst_change_date is 21 days from the 1st dst_change_day
if datepart(weekday,@DSTDT) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,28,@DSTDT)
else
if datepart(weekday,dateadd(day, 1, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(weekday,dateadd(day,1,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 2, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(weekday,dateadd(day,2,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 3, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(weekday,dateadd(day,3,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 4, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,4,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 5, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,5,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 6, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,6,@DSTDT))),@DSTDT)
end
if @Days_in_month = 28
--in February (not a leap year) the last occurrence of the day will always be 21 days after the
--first occurrence of the day.
if datepart(weekday,@DSTDT) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,21,@DSTDT)
else
if datepart(weekday,dateadd(day, 1, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,1,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 2, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,2,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 3, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,3,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 4, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,4,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 5, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,5,@DSTDT))),@DSTDT)
else
if datepart(weekday,dateadd(day, 6, @DSTDT)) = @DST_CHANGE_DAY
SET @DSTDT = DATEADD(day,(20 + datepart(day,dateadd(day,6,@DSTDT))),@DSTDT)
SET @DSTDT = DATEADD(hour,@DSTHOUR,@DSTDT)
SET @DSTDT = DATEADD(minute,@DSTMINUTE,@DSTDT)
END
ELSE
BEGIN -- Calculations for date other than last day
-- 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 @DSTMODIFIER = DATEPART(weekday,@DSTDT)
-- 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 @DSTMODIFIER > @DST_CHANGE_DAY
BEGIN
SET @DSTMODIFIER = 8 - @DSTMODIFIER
END
ELSE
BEGIN
SET @DSTMODIFIER = (@DSTMODIFIER-1)*-1
END
-- This is the main calculation to determine the date of the MMWDHHmm pattern
SET @DSTDT = DATEADD(day,(@DST_CHANGE_DAY_OCCURRENCE_INT*7)-(8-@DST_CHANGE_DAY) + @DSTMODIFIER,@DSTDT)
SET @DSTDT = DATEADD(hour,@DSTHOUR,@DSTDT)
SET @DSTDT = DATEADD(minute,@DSTMINUTE,@DSTDT)
-- Declare variables needed to convert MMWDHHmm pattern to for DST stop date
END
RETURN @DSTDT
END
GO
|
|
|
|
|
This is awesome, thanks guys...
however I can't seem to copy the code from 'Colin 2' (Apr2010) code blocks, when I do it's not formatted & several lines are all on 1 line. Not sure what I'm doing wrong 
Also which version of Robert's code did u use pls? v5 as at 3Apr2010 or v2 on the main page as at 1Dec2008?
modified on Wednesday, July 21, 2010 1:59 AM
|
|
|
|
|
shell_l_d,
For closure sake I am mentioning that I answered your questions via another response that you posted under the Code Change section under the version 5 of the code.
Robert Ford
|
|
|
|
|
Recently I discovered a performance issue with using this funciton as an inline function in a query that retrieved 10s of thousands of records. With out the function the query ran in only 10 seconds. When I added the function into the where clause the query performance degrated to over 10 minutes. For a real-time reporting application this is not acceptable.
Resolution:
Instead of having the function in the where clause of the query I added some variables to the process. I typically write all my queries as stored procedures instead of views since it provides many additional options. In my procedure I declared a datetime variable and used the function to convert the date range variables to GMT and then used that variable in my where clause of the query as oppose to using the function to convert the GMT values to central time. This got the performance of the query back down to between 10 and 20 seconds.
For those that are wondering why I have pulling 10 thousand or more records into a real-time application, I really am not. This is an aggregation job that creates aggregates on a real-time basis for the reporting and getting the time down is very important to make sure that data is presented accuratly.
Robert Ford
|
|
|
|
|
It seems that I am not able to modify the article any more so I started a thread for code changes. I will reply to this thread each time there is a change to the code and provide the reasons for the change.
Robert Ford
|
|
|
|
|
Thanks to Tom Medley for pointing out an issue with the code in situtations where the DST end range date is in a month that does not start on a Sunday. Here is the updated version of that procedure (I put version 3 because I had made a change to it previously due to a previous issue pointed out Amj Yamsani)
-- =============================================
-- 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
Robert Ford
|
|
|
|
|
--
-- 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
|
|
|
|
|
-- *************************
--
-- 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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
