Time Zone Conversions in SQL

Hi..

While surfing net i found a beautiful article..

The followiing function convert the Time zones based on the input given.. check the functions and also a table called time zone should be created that consists of OfSet values


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_TimezoneSELECT @Destination_DST = DST_bit FROM TIMEZONEWHERE 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_TimezoneEND
--------------------------------------------------------------------------------------------------------------- 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_TimezoneEND
--------------------------------------------------------------------------------------------------------------- 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


************TimeZoneTable**********************


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]GOALTER TABLE [dbo].[TIMEZONE] ADD CONSTRAINT [PK_TIMEZONE] PRIMARY KEY CLUSTERED ( [Timezone_Name] ) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

*******************************Time Zone Values ************************

+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
IST 330 0
*****************************************************************************
CREATE FUNCTION udf_LastSundayOfTheMonth
( @Date datetime )
RETURNS datetime
AS
BEGIN
DECLARE @weekday int
DECLARE @Lastday datetime
DECLARE @number int
DECLARE @day datetime
SELECT @weekday = 0SELECT @Lastday = (DATEADD(day, -1, CAST(STR(MONTH(@Date)+1) + '/' + STR(01) + '/' + STR(YEAR(@Date)) AS DateTime)))
SELECT @number = DATEPART(day, @Lastday)WHILE @weekday <> 1BEGINSELECT @day = (CAST(STR(MONTH(@Date)) + '/' + STR(@number) + '/' + STR(YEAR(@Date)) AS DateTime))
SELECT @weekday = DATEPART(weekday, @day)
SELECT @number = @number - 1ENDRETURN @day
END
GO

**************************************************************

CREATE FUNCTION udf_FirstSundayOfTheMont
h( @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


ENDGO



And Finally how to use the function



select dbo.udf_Timezone_Conversion('EST','IST',getdate(),0)


This completes the function ..

Hope this will be helpful for u all

Happppppppy Programming