SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Daylight Savings Time correction
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 08/26/2003 :  10:28:08  Show Profile
Hello,

I’m wondering what is solution with automatic correction for Daylight Savings Time?

I’d like to convert from UTC (column) to local_time but with automatic correction when the time changes.

Now I use just:

Computed column:

local_time AS DATEADD(hh, -6, UTC)

or

local_time AS DATEADD(hh, -7, UTC) in winter

What is the best approach in this case?


Thx,

Dobby

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 08/26/2003 :  12:49:03  Show Profile  Visit tkizer's Homepage
I wrote a UDF that can convert between two given timezones. It requires a lookup table to exist with timezone information.


----------------------------------------------------------------------------------------------------
-- OBJECT NAME		: udf_Timezone_Conversion
--
-- AUTHOR		: Tara Duggan
-- DATE			: June 5, 2002
--
-- INPUTS		: @Source_Timezone, @Destination_Timezone, 
--			  @Source_datetime, @Display_Timezone
-- OUTPUTS		: @converted_datetime
--
-- DEPENDENCIES		: None
--
-- APPLICATION(s)	: All applications can utilize this function.
--
-- DESCRIPTION		: This function converts a given date and time 
--			  from one time zone to another.
--                          
-- EXAMPLES (optional)	: SELECT dbo.udf_Timezone_Conversion('PST', 'EST', getdate(), 0)
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE FUNCTION udf_Timezone_Conversion
(@Source_Timezone varchar(25), @Destination_Timezone varchar(25), 
@Source_datetime datetime, @Display_Timezone bit = 0)
RETURNS varchar(50)
AS

BEGIN
-------------------------------------------------------------------------------------------------------------
--  Declarations
-------------------------------------------------------------------------------------------------------------
DECLARE @Source_DST bit
DECLARE @Destination_DST bit
DECLARE @converted_date datetime
DECLARE @converted_timezone varchar(50)
DECLARE @year int
DECLARE @AprilDate datetime
DECLARE @OctDate datetime
DECLARE @DST_Start datetime
DECLARE @DST_End datetime
DECLARE @GMT_Offset_Source int
DECLARE @GMT_Offset_Destination int
DECLARE @converted_datetime varchar(50)
-------------------------------------------------------------------------------------------------------------
--  Initializations
-------------------------------------------------------------------------------------------------------------
SELECT @year = DATEPART(year, @Source_datetime)
SELECT @AprilDate = 'Apr 15 ' + CONVERT(char(4), @year) 
SELECT @OctDate = 'Oct 15 ' + CONVERT(char(4), @year) 
SELECT @DST_Start = DATEADD(hour, 2, (dbo.udf_FirstSundayOfTheMonth(@AprilDate)))
SELECT @DST_End = DATEADD(hour, 2, (dbo.udf_LastSundayOfTheMonth(@OctDate)))
SELECT @DST_End = DATEADD(second, -1, @DST_End)

SELECT @GMT_Offset_Source = GMT_Offset
FROM TIMEZONE
WHERE Timezone_Name = @Source_Timezone

SELECT @GMT_Offset_Destination = GMT_Offset
FROM TIMEZONE
WHERE Timezone_Name = @Destination_Timezone

SELECT @Source_DST = DST_bit
FROM TIMEZONE
WHERE Timezone_Name = @Source_Timezone

SELECT @Destination_DST = DST_bit
FROM TIMEZONE
WHERE Timezone_Name = @Destination_Timezone
-------------------------------------------------------------------------------------------------------------
--  Check for valid inputs
-------------------------------------------------------------------------------------------------------------
IF @Source_Timezone NOT IN (SELECT Timezone_Name FROM TIMEZONE) 
  OR @Destination_Timezone NOT IN (SELECT Timezone_Name FROM TIMEZONE)
	RETURN 'You have entered an invalid time zone.'
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and both time zones observe DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1) AND (@Source_DST = 1)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source - 60, @Source_datetime)
			
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination + 60, @converted_date)
		
	IF @converted_date NOT BETWEEN @DST_Start AND @DST_End
	BEGIN
		SELECT @converted_timezone = @Destination_Timezone
		SELECT @converted_date = DATEADD(MINUTE, -60, @converted_date)
	END
	ELSE
		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
END	
-------------------------------------------------------------------------------------------------------------
--  Source data and time are not during DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination data and time are in DST after the conversion
--  If destination date and time are in DST, check if it observes DST
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime NOT BETWEEN @DST_Start AND @DST_End) 
  OR ((@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 0) AND (@Source_DST = 0))
BEGIN 
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source, @Source_datetime)
	
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination, @converted_date)

	IF (@converted_date BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1)
	BEGIN
		SELECT @converted_date = DATEADD(MINUTE, 60, @converted_date)

		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
	END
	ELSE
		SELECT @converted_timezone = @Destination_Timezone
END	
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and only source time zone observes DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 0) AND (@Source_DST = 1)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source - 60, @Source_datetime)
	
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination, @converted_date)
		
	SELECT @converted_timezone = @Destination_Timezone
END
-------------------------------------------------------------------------------------------------------------
--  Source date and time are during DST and only destination time zone observes DST
--  Convert source time zone to GMT, then convert GMT to destination time zone
--  Check if destination date and time are not in DST after the conversion
-------------------------------------------------------------------------------------------------------------
ELSE IF (@Source_datetime BETWEEN @DST_Start AND @DST_End) AND (@Destination_DST = 1) AND (@Source_DST = 0)
BEGIN
	SELECT @converted_date = DATEADD(MINUTE, - @GMT_Offset_Source, @Source_datetime)
		
	SELECT @converted_date = DATEADD(MINUTE, @GMT_Offset_Destination + 60, @converted_date)
		
	IF @converted_date NOT BETWEEN @DST_Start AND @DST_End
	BEGIN
		SELECT @converted_timezone = @Destination_Timezone
		SELECT @converted_date = DATEADD(MINUTE, -60, @converted_date)
	END
	ELSE
		SELECT @converted_timezone = DST_Abbrv
		FROM TIMEZONE
		WHERE Timezone_Name = @Destination_Timezone
END
-------------------------------------------------------------------------------------------------------------
--  Format the output using style 21
-------------------------------------------------------------------------------------------------------------	
IF @Display_Timezone = 0
	SELECT @converted_datetime = CONVERT(varchar(50), @converted_date, 21)
ELSE 
	SELECT @converted_datetime = CONVERT(varchar(50), @converted_date, 21) + '; ' + UPPER(@converted_timezone)
-------------------------------------------------------------------------------------------------------------
--  Return the output
-------------------------------------------------------------------------------------------------------------
RETURN @converted_datetime

END




Here is the table definition:


CREATE TABLE [dbo].[TIMEZONE] (
	[Timezone_Name] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[GMT_Offset] [float] NOT NULL ,
	[DST_Abbrv] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DST_bit] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TIMEZONE] ADD 
	CONSTRAINT [PK_TIMEZONE] PRIMARY KEY  CLUSTERED 
	(
		[Timezone_Name]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO





Here is the data (only the below data is supported by this function):

+0200 	120.0	      	0

+0300 	180.0	      	0

+0400 	240.0	      	0

+0500 	300.0	      	0

+0530 	330.0	      	0

+0600 	360.0	      	0

+0700 	420.0	      	0

+0800 	480.0	      	0

+0930 	570.0	      	0

+1000 	600.0	      	0

+1100 	660.0	      	0

+1200 	720.0	      	0

-0300 	-180.0	      	0

-0400 	-240.0	      	0

-0500 	-300.0	      	0

-1100 	-660.0	      	0

-1200 	-720.0	      	0

AHST	-600.0	HDT	1

ALASKA	-540.0	ALASKA	1

ARIZ  	-420.0	      	0

AST   	-240.0	ADT   	1

CST   	-360.0	CDT   	1

EST   	-300.0	EDT   	1

GMT	0.0	 	0

HKT   	480.0	      	0

HST	-600.0	      	0

INDANA	-300.0	      	0

IRAN  	210.0	      	0

JST   	540.0	      	0

KST   	270.0	      	0

MEXCTY	-360.0	MEXCDT	1

MST   	-420.0	MDT   	1

NFLD  	-210.0	NF    	1

PST   	-480.0	PDT   	1

QUEBECE	-240.0	 	0

SASKAT	-360.0	      	0



HTH,

Tara
Go to Top of Page

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 08/27/2003 :  14:38:48  Show Profile
It might be too overwhelming for me!

Thank you very much!

Dobby
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 08/27/2003 :  14:42:20  Show Profile  Visit tkizer's Homepage
What is too overwhelming?

It is already written. You just need to create the table, create the UDF, then insert the data.

The UDF does exactly what you need it to do. It just handles lots of timezones.

You don't even have to change anything to get it to work for you.

Once it is implemented (just add two timezones to the table to test it), you can call it like this:

SELECT dbo.udf_Timezone_Conversion('GMT', 'PST', getdate(), 0)

GMT is the same as UTC.

The first parameter is the from timezone, the second is the to timezone, the third is the date and time,
and the last parameter just says whether or not to display the timezone in the output.

Tara
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/27/2003 :  16:25:08  Show Profile  Visit jsmith8858's Homepage
does it require the FirstSundayOfMonth and the LastSundayOfMonth UDF's ?

- Jeff
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 08/27/2003 :  16:36:15  Show Profile  Visit tkizer's Homepage
Yes it does. Here they are:

[code]
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : udf_LastSundayOfTheMonth
--
-- AUTHOR : Tara Duggan
-- DATE : June 5, 2002
--
-- INPUTS : @Date
-- OUTPUTS : @Day
--
-- DEPENDENCIES : None
--
-- APPLICATION(s) : This function is called by another function, udf_Timezone_Conversion.
--
-- DESCRIPTION : This function returns the date of the last Sunday of the month
-- when given any date during that month.
--
-- EXAMPLES (optional) : SELECT dbo.udf_LastSundayOfTheMonth(getdate())
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE FUNCTION udf_LastSundayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @Lastday datetime
DECLARE @number int
DECLARE @day datetime
SELECT @weekday = 0
SELECT @Lastday = (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)))
SELECT @number = DATEPART(day, @Lastday)
WHILE @weekday <> 1
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number - 1
END
RETURN @day
END

GO

----------------------------------------------------------------------------------------------------
-- OBJECT NAME : udf_FirstSundayOfTheMonth
--
-- AUTHOR : Tara Duggan
-- DATE : June 5, 2002
--
-- INPUTS : @Date
-- OUTPUTS : @Day
--
-- DEPENDENCIES : None
--
-- APPLICATION(s) : This function is called by another function, udf_Timezone_Conversion.
--
-- DESCRIPTION : This function returns the date of the first Sunday of the month
-- when given any date during that month.
--
-- EXAMPLES (optional) : SELECT dbo.udf_FirstSundayOfTheMonth(getdate())
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- MM/DD/YYYY - (Name)
-- (Description)
----------------------------------------------------------------------------------------------------
CREATE FUNCTION udf_FirstSundayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @day datetime
DECLARE @number int
SELECT @number = 1
SELECT @weekday = 0
WHILE @weekday <> 1
BEGIN
SELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number + 1
END
RETURN @day
END

GO






Tara
Go to Top of Page

sappollo
Starting Member

1 Posts

Posted - 02/09/2004 :  08:39:48  Show Profile
Hi all,

I have built similar function last year. Recently I have discovered that my function takes too long to retrieve a 100000 recordtable with timezone correction due to the fact that 100000 times start of summertime is generated, 100000 times end of summertime is generated and 100000 the default offset is queried.

I made the same function for Oracle, and I adjusted this function to speed up data retrieval. The clue was to load in memory 2 tables when the user first time uses the function during a session. Table 1 holds the default offset of the timezone (which is the same for all records during retrieval) and table 2 contains start of summertime and end of summertime from year 1990 till 2030. I'm no SQL server expert, so I'm looking for a similar approach. During my search quest I ended up in this thread and I hope someone could give me a direction to success, because if I read the function made by Tara correctly, she has exactly the same problems I would have (no caching og start and end of summertime and each requery the same timezone table).

Kind regards,

Sappollo
Go to Top of Page

Paw Jershauge
Starting Member

2 Posts

Posted - 04/02/2007 :  17:28:47  Show Profile
I know its a bit late to put this on the site, but why not just make a function that converts UTC Time to local time and call that???
This code is for danish users... we have to add 1 hour in the winter and add 2 hours in the summer..

So if you need to add 0 hours in the winter and 1 hour in the summer, just change these lines:
SET @RETURNDATA = DATEADD(hh,2,@InDateTime)
to
SET @RETURNDATA = DATEADD(hh,1,@InDateTime)

and
SET @RETURNDATA = DATEADD(hh,1,@InDateTime)
to
SET @RETURNDATA = DATEADD(hh,0,@InDateTime)




CREATE FUNCTION dbo.ConvertToLocalTime(@InDateTime datetime)
-- Developed by Paw Jershauge
RETURNS DATETIME
AS
BEGIN
           DECLARE @WorkingYear varchar(4)
           DECLARE @StartDay varchar(2)
           DECLARE @EndDay varchar(2)
           DECLARE @SUMMERTIMESTART DATETIME
           DECLARE @SUMMERTIMEEND DATETIME
           DECLARE @RETURNDATA DATETIME
           SET @StartDay = CONVERT(varchar(2),(31 - (5 * Year(@InDateTime)/4 + 4) % 7))
           SET @EndDay = CONVERT(varchar(2),(31 - (5 * Year(@InDateTime)/4 + 1) % 7))
           SET @WorkingYear = CONVERT(varchar(4),Year(@InDateTime))
           SET @SUMMERTIMESTART = CONVERT(DATETIME,@WorkingYear + '-03-' + @StartDay + ' 01:00:00',20)
           SET @SUMMERTIMEEND = CONVERT(DATETIME,@WorkingYear + '-10-' + @EndDay + ' 00:00:00',20)
           IF DateDiff(hh,@SUMMERTIMESTART,@InDateTime) >= 0 AND DateDiff(hh,@SUMMERTIMEEND,@InDateTime) <= 0
           SET @RETURNDATA = DATEADD(hh,2,@InDateTime)
           ELSE
           SET @RETURNDATA = DATEADD(hh,1,@InDateTime)
           RETURN @RETURNDATA    -- Retun Value
END

Edited by - Paw Jershauge on 04/04/2007 13:02:20
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37133 Posts

Posted - 04/04/2007 :  13:10:36  Show Profile  Visit tkizer's Homepage
We can't use that as we have to do a lot of timezone conversions. We have to read the user's timezone from a table to determine what to do in the conversion. We don't just convert from one to the next. Our source is almost always GMT though. Our destination can be one of about 10 timezones or so.

Plus our way doesn't require any code changes when DST changes.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000