Click here to Skip to main content
Click here to Skip to main content

SQL 2005 Time Zone Conversion Functions

, 30 Nov 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
How to create simple SQL time zone functions to use in queries.

Introduction

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

Using the Code

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    -- Return the new converted datetime
    RETURN @NEWDT
    
END

Final Solution Architecture

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

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

Points of Interest

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

Revision History

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

License

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

Share

About the Author

robertford
Other
United States United States
No Biography provided

Comments and Discussions

 
QuestionPrecisely the workaround for MS SQL 2005 I needed Pinmembereddiemjm432126-Sep-14 0:39 
SuggestionA new alternative solution for such issues: T-SQL Toolbox on CodePlex PinmemberAndreas Degen3-May-14 1:53 
QuestionProblem with Time Zone calculation for this year PinmemberMark Kilroy11-Mar-14 13:02 
QuestionTimezones info for Europe PinmemberMaikel_D9-Apr-13 22:31 
QuestionExcellent PinmemberPatel,N25-Jan-13 11:11 
QuestionWhich is better? PinmemberAndyTexas15-Nov-10 6:54 
AnswerRe: Which is better? Pinmemberrobertford16-Nov-10 15:19 
GeneralRe: Which is better? Pinmembershell_l_d6-Feb-12 18:30 
GeneralSuggested updates PinmemberColin 215-Apr-10 2:17 
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

QuestionRe: Suggested updates [modified] Pinmembershell_l_d20-Jul-10 17:13 
AnswerRe: Suggested updates Pinmemberrobertford21-Jul-10 4:22 
GeneralUsing the function in very large data sets Pinmemberrobertford11-Jun-09 19:27 
GeneralCode Change Pinmemberrobertford11-Jun-09 19:04 
GeneralGET_TZTIME v3 Pinmemberrobertford11-Jun-09 19:14 
GeneralTime Zone Functions v4 Pinmemberrobertford21-Jan-10 15:35 
GeneralTime Zone Functions v5 Pinmemberrobertford2-Apr-10 19:05 
GeneralRe: Time Zone Functions v5 [modified] Pinmembershell_l_d20-Jul-10 20:31 
GeneralRe: Time Zone Functions v5 Pinmemberrobertford21-Jul-10 4:19 
GeneralRe: Time Zone Functions v5 [modified] Pinmembershell_l_d21-Jul-10 5:18 
GeneralRe: Time Zone Functions v5 Pinmembershell_l_d21-Jul-10 17:13 
AnswerRe: Time Zone Functions v5 [modified] Pinmembershell_l_d22-Jul-10 6:09 
GeneralRe: Time Zone Functions v5 PinmemberCallon Campbell4-Aug-10 5:22 
GeneralRe: Time Zone Functions v5 [modified] Pinmembershell_l_d4-Aug-10 18:42 
GeneralRe: Time Zone Functions v5 Pinmemberrobertford6-Aug-10 6:53 
GeneralRe: Time Zone Functions v5 PinmemberVictorwzq19-Dec-11 9:54 
GeneralRe: Time Zone Functions v5 Pinmembershell_l_d6-Feb-12 18:28 
GeneralRe: Time Zone Functions v5 Pinmemberrobertford6-Aug-10 6:47 
GeneralRe: Time Zone Functions v5 PinmemberMadburg13-Oct-10 8:59 
GeneralRe: Time Zone Functions v5 Pinmembershell_l_d6-Feb-12 18:29 
QuestionIs ABS() correct in GET_UTCTIME ? PinmemberTom Medley10-Jun-09 12:59 
GeneralSome Time Zones not addressed PinmemberTom Medley10-Jun-09 12:00 
GeneralRe: Some Time Zones not addressed Pinmemberrobertford11-Jun-09 19:19 
QuestionPossible Problem PinmemberTom Medley10-Jun-09 4:43 
AnswerRe: Possible Problem Pinmemberrobertford11-Jun-09 19:10 
QuestionHow to call this function Pinmembertrushitshah26-May-09 1:46 
AnswerRe: How to call this function Pinmemberrobertford10-Jun-09 15:16 
GeneralChange in daylight savings Pinmemberstribbed26-Nov-08 19:55 
GeneralRe: Change in daylight savings Pinmemberrobertford30-Nov-08 18:10 
GeneralRe: Change in daylight savings Pinmemberanj yamsani26-Mar-09 10:15 
GeneralRe: Change in daylight savings Pinmemberrobertford10-Jun-09 15:08 
QuestionRe: Change in daylight savings PinmemberSoftCity18-Sep-09 13:00 
AnswerRe: Change in daylight savings Pinmemberrobertford30-Sep-09 17:02 
GeneralRe: Change in daylight savings PinmemberSoftCity1-Oct-09 17:03 
GeneralRe: Change in daylight savings Pinmemberrobertford2-Oct-09 1:47 
GeneralRe: Change in daylight savings PinmemberRahul Singla23-Dec-09 9:07 
GeneralRe: Change in daylight savings Pinmemberrobertford15-Jan-10 18:29 
GeneralRe: Change in daylight savings PinmemberRahul Singla15-Jan-10 18:58 
GeneralRe: Change in daylight savings Pinmemberrobertford21-Jan-10 15:28 
GeneralRe: Change in daylight savings Pinmemberjoel_charlebois23-Jan-10 8:00 
GeneralRe: Change in daylight savings Pinmemberrobertford2-Apr-10 19:13 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 30 Nov 2008
Article Copyright 2008 by robertford
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid