Yet another option.... The following T-SQL functions convert date/time to/from Remedy and account for GMT and Daylight Savings Time (set up for Canada):
/* NOTES on this function: Input parameters @GetUTCDate and @GetDate expect the results of the GetUTCDate() and GetDate() built-in functions respectively. These functions won't work within the context of a UDF. @RemedySeconds is the value from a Remedy date field. SAMPLE USE: declare @result datetime declare @remedyseconds int select @remedyseconds=1140120203 --create_date from shr_categorization where entry_id=000000000001583 select @result=msdb.dbo.fnGetRemedyDate(@remedyseconds,GetUTCDate(),GetDate ()) print @result Acknowledgements: http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i- convert-local-time-to-utc-gmt-time.html */ USE MSDB GO ALTER FUNCTION fnGetRemedyDate(@RemedySeconds int, @GetUTCDate datetime, @GetDate datetime) RETURNS datetime AS BEGIN DECLARE @sdt smalldatetime, @edt smalldatetime, @i tinyint, @Offset int, @RemedyDate datetime, @UTCDate smalldatetime SELECT @UTCDate = DATEADD(second, @RemedySeconds, '1970-01-01') -- value of Remedy (epoch) time GMT -- find first Sunday in April SET @i = 1 WHILE @i < 7 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 SELECT @Offset = DATEDIFF ( ss , @GetUTCDate , @GetDate ) /* NOTE that the adjustment below is slightly flawed; you are comparing the initial value in GMT/UTC time to the start/end times of DST in the local time zone (since they can't be adjusted for UTC without knowing the adjustment factor)! A more accurate (but also complex) methodology involves use of the registry as described at http://www.planet-source-code.com/vb/scripts/ShowCode.asp? txtCodeId=544&lngWId=5 */ IF (@UTCDate>[EMAIL PROTECTED] AND @UTCDate<@edt) SET @offset = @offset + 3600 -- an hour's worth of seconds SELECT @RemedyDate = DATEADD(second,(@RemedySeconds + @Offset), '1970-01-01') RETURN(@RemedyDate) END /* SAMPLE USE: declare @TargetDate datetime declare @RemedyDate datetime set @TargetDate='2006-02-09' select @RemedyDate=msdb.dbo.fnSetRemedyDate(@TargetDate,GetUTCDate(),GetDate ()) print @RemedyDate */ ALTER FUNCTION fnSetRemedyDate(@TargetDate datetime, @GetUTCDate datetime, @GetDate datetime) RETURNS int AS BEGIN -- work out GMT/UTC offset value DECLARE @Offset int, @sdt smalldatetime, @edt smalldatetime, @i tinyint -- find first Sunday in April SET @i = 1 WHILE @i < 7 BEGIN SET @sdt = RTRIM(YEAR(@TargetDate))+'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(@TargetDate))+'10'+RTRIM(@i) + ' 02:00' IF DATEPART(weekday,@edt)=1 BEGIN SET @i = 24 END SET @i = @i - 1 END SELECT @Offset = DATEDIFF ( hh , @GetDate, @GetUTCDate ) -- adjust for DST (see NOTE in fnGetRemedyDate) IF (@TargetDate>[EMAIL PROTECTED] AND @TargetDate<@edt) SET @Offset = @Offset - 1 -- an hour's worth of seconds --apply offset value to target date (so date is entered in GMT time, not local time) SELECT @TargetDate = dateadd(hh,@Offset,@TargetDate) -- determine Remedy int value for revised target date DECLARE @RemedyDate int SELECT @RemedyDate=ABS(DATEDIFF(ss,@TargetDate,'1970-01-01')) RETURN(@remedyDate) END /* SAMPLE USE: declare @result int declare @GetUTCDate datetime select @result=msdb.dbo.fnSetRemedyDate(GetUTCDate()) print @result */ CREATE FUNCTION fnSetRemedyCurrentDate(@GetUTCDate datetime) RETURNS int AS BEGIN DECLARE @RemedyDate int SELECT @RemedyDate=ABS(DATEDIFF(ss,@GetUTCDate,'1970-01-01')) RETURN(@remedyDate) END _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the Answers Are"
