A while back I published and article on a time zone function for MS SQL. A few revisions have occurred since then. The current version of the code is listed below.
table:
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 tableCREATE 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 keyALTER 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 contraintsALTER 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 ('11110100') 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 tableINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GOINSERT 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','11110100')
GO
functions:
CREATE FUNCTION [dbo].[UTCTIME]
(@DT AS DATETIME, @TZ AS VARCHAR(12))
RETURNS DATETIMEASBEGIN -- DECLARE VARIABLESDECLARE @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_zonesWHERE 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 dateDECLARE @DSTSTARTMONTH INT
DECLARE @DSTSTARTINSTANCE INT
DECLARE @DSTSTARTDAY INT
DECLARE @DSTSTARTHOUR INT
DECLARE @DSTSTARTMINUTE INT
DECLARE @DSTSTARTMODIFIER INT
-- Perform calculations to determine dateSET @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 evaluatedSET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Add month value for start pattern to the start date variableSET @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 patternSET @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 dateDECLARE @DSTSTOPMONTH INT
DECLARE @DSTSTOPINSTANCE INT
DECLARE @DSTSTOPDAY INT
DECLARE @DSTSTOPHOUR INT
DECLARE @DSTSTOPMINUTE INT
DECLARE @DSTSTOPMODIFIER INT
-- Perform calculations to determine dateSET @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 evaluatedSET @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 patternSET @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/timesIF @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 @NEWDTENDGOCREATE FUNCTION [dbo].[TZTIME]
(@DT AS DATETIME, @TZ AS VARCHAR(12))
RETURNS DATETIMEASBEGIN-- DECLARE VARIABLESDECLARE @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_zonesWHERE 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 dateDECLARE @DSTSTARTMONTH INT
DECLARE @DSTSTARTINSTANCE INT
DECLARE @DSTSTARTDAY INT
DECLARE @DSTSTARTHOUR INT
DECLARE @DSTSTARTMINUTE INT
DECLARE @DSTSTARTMODIFIER INT
-- Perform calculations to determine dateSET @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 evaluatedSET @DSTSTARTDT = CONVERT(DATETIME,'1/1/' + CONVERT(VARCHAR(4),DATEPART(year,@NEWDT)))
-- Add month value for start pattern to the start date variableSET @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 patternSET @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 dateDECLARE @DSTSTOPMONTH INT
DECLARE @DSTSTOPINSTANCE INT
DECLARE @DSTSTOPDAY INT
DECLARE @DSTSTOPHOUR INT
DECLARE @DSTSTOPMINUTE INT
DECLARE @DSTSTOPMODIFIER INT
-- Perform calculations to determine dateSET @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 evaluatedSET @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 patternSET @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/timesIF @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 @NEWDTENDGOCREATE FUNCTION [dbo].[NEW_TIME]
(@DT AS DATETIME, @TZ1 AS VARCHAR(12),
@TZ2 AS VARCHAR(12))
RETURNS DATETIMEASBEGIN -- Declare variablesDECLARE @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 timeIF NOT @TZ1 IN ('GMT','UTC')
BEGIN SELECT @NEWDT = dbo.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 timezoneIF NOT @TZ2 IN ('GMT','UTC')
BEGIN SELECT @NEWDT = dbo.TZTIME(@NEWDT,@TZ2) END -- Return the new converted datetime RETURN @NEWDTENDGO
Do you have settings for time zones outside US?
ReplyDeleteThank you
Do you know where I can find similar settings for time zones outside of US?
ReplyDeleteNina,
ReplyDeleteSorry it took me a few days to respond. I was out of town and away from computers in general.
This process can support any of the time zones in the world. The specific value that you should use depends upon the time zone you are looking for. If you are not sure on the timezone for a specific location you can use the website, http://www.timeanddate.com/time/map/. If you need help determining the value let me know and I would be glad to help.
Rob, Thank you for your reply.
ReplyDeleteDo you have any suggestion about handling daylight saving differences?
Nina, I am sorry somehow I missed this last comment. The process is setup to handle any time zone adjustment. The code is an eight digit value (e.g. 03210200 which is first two digits represents month, third represents the week of the month, fourth represents the day of week and the last four represents the time when the adjustment starts). So for most US time zones daylight savings starts in March during the second week on the first day of the week at 2:00 am which would be 03210200. Another example is Zurich. It is in Central European Time Zone. The daylight savings there starts the last week in March on Sunday at 2:00 am. The code would be 03510200. I hope that this helps.
ReplyDelete