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.
(Previously posted Feb 2009)
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
