I use the following function (which also attempts to account for time
zone offsets and DST):
USE [msdb]
GO
/****** Object: UserDefinedFunction [dbo].[fnGetRemedyDate2] Script
Date: 02/12/2009 10:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnGetRemedyDate2](@RemedySeconds int)
RETURNS datetime
AS
BEGIN
/* In it's DST adjustment mechanism, WIN2K does not take into account
DST settings for years prior to 2007.
In other words, dates in 2006 and prior are adjusted as though the new
DST rules apply.
Therefore dates being returned by this function for years preceding 2007
require a different adjustment for the period
from the beginning of old DST (1st Sunday in April) to the end of old
DST (last Sunday in October) than do the years
from 2007 forward which adjust DST from the beginning of new DST (2nd
Sunday in March) to the end of new DST (1st Sunday in November).
*/
DECLARE
@strSDT varchar(15),
@sdt smalldatetime,
@edt smalldatetime,
@i tinyint,
@Offset int,
@DSTBias int,
@RemedyDate datetime,
@UTCDate datetime,
@Year int
SELECT @UTCDate = DATEADD(second, @RemedySeconds, '1970-01-01')
-- value of Remedy (epoch) time GMT
-- determine standard offset value by reading from registry with
undocumented extended sp xp_regread
EXEC master.dbo.xp_regread
@rootkey='HKEY_LOCAL_MACHINE',@key='SYSTEM\CurrentControlSet\Control\Tim
eZoneInformation',
@value_name='Bias', @val...@offset OUTPUT
-- Bias reads in minutes; convert standard offset to seconds
SELECT @Offset = @Offset * 60
-- get DST bias value from registry
EXEC master.dbo.xp_regread
@rootkey='HKEY_LOCAL_MACHINE',@key='SYSTEM\CurrentControlSet\Control\Tim
eZoneInformation',
@value_name='DaylightBias', @val...@dstbias OUTPUT
-- Bias reads in minutes; convert standard offset to seconds
SELECT @DSTBias = @DSTBias * 60
-- do analysis to determine year - adjustment may be required
based on DST bias (do this with GMT time only-if date is on cusp of
year, it will not affect DST)
SELECT @Year=YEAR(DATEADD(second,@RemedySeconds, '1970-01-01'))
IF (@Year > 2006)
BEGIN
-- find second Sunday in March - between 8th and 14th
SET @i = 8
WHILE @i < 15
BEGIN
SET @strSDT = RTRIM(YEAR(@UTCDate))+'03'
IF (@i < 10)
SET @strSDT = @strSDT + '0'
SET @strSDT = @strSDT +RTRIM(@i)+' 02:00'
SET @s...@strsdt
IF DATEPART(weekday,@sdt)=1
BEGIN
SET @i = 14
END
SET @i = @i + 1
END
-- find first Sunday in November
SET @i = 1
WHILE @i < 8
BEGIN
SET @edt =
RTRIM(YEAR(@UTCDate))+'110'+RTRIM(@i)+' 02:00'
IF DATEPART(weekday,@edt)=1
BEGIN
SET @i = 7
END
SET @i = @i + 1
END
END
ELSE
BEGIN
-- find first Sunday in April
SET @i = 1
WHILE @i < 8
BEGIN
SET @sdt =
RTRIM(YEAR(@UTCDate))+'040'+RTRIM(@i)+' 02:00'
IF DATEPART(weekday,@sdt)=1
BEGIN
SET @i = 7
END
SET @i = @i + 1
END
-- find last Sunday in October
SET @i = 31
WHILE @i > 24
BEGIN
SET @edt = RTRIM(YEAR(@UTCDate))+'10'+RTRIM(@i)
+ ' 02:00'
IF DATEPART(weekday,@edt)=1
BEGIN
SET @i = 24
END
SET @i = @i - 1
END
END
IF (@UTCDate>=...@sdt AND @UTCDate<@edt)
SET @offset = @offset + @DSTBias -- adjust offset by
value of DST bias obtained from registry above
SELECT @RemedyDate = DATEADD(second,(@RemedySeconds - @Offset),
'1970-01-01')
RETURN(@RemedyDate)
END
Cheers,
Jim.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
