Hey Roger: ------------------------------------------------------------------- DB_Reference_69398.pdf Page 131 -132 ------------------------------------------------------------------- To convert the date and time format for a Sybase or Microsoft SQL Server database:
1 Using any front-end tool that enables direct access to a Sybase or Microsoft SQL Server database, log in as a user who has write access to the AR System tables. 2 Type the following command: % select dateadd(second, C<column_number> + <offset>, "Jan 1, 1970") from T<table_number> where <column_number> is the number of the column for the date and time field, <table_number> is the number of the form table, and <offset> is a positive or negative number representing the number of seconds later or earlier than GMT. 3 Optionally, you could format the date field by using the convert function. On Feb 12, 10:18 am, "Ashton, Jim (JUS)" <[email protected]> wrote: > 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 atwww.arslist.org > Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: RMI Solutions ARSlist: "Where the Answers Are"
