Dates / Times and Daylight Saving 1

[clawton]

I have written several Stored Procedures that insert new records into various parts of the database of a separate commercial application. This app has been work fine for about 4 months, but started giving strange results 2 weeks ago.

The application stores dates/times on the records as stamps of the number of seconds since 01-01-1970 00:00:00 (Behold those UNIX programmers , so 2002-04-13 10:15:01 is stored as 1018692901. These records are then escalated by other processes (part of the commercial app) after set periods of time.

It has now come to light that these date/time stamps go from 01-01-1970 00:00:00, but ignores the Daylight Saving setting on the local machine.

My SQL Stored procedures just encode the current GetDate() which is adjusted for DS.

Does anyone know of a way that I can in my Stored Procedures :

1. Return the current date, but ignoring the DS setting

2. Detect whether DS is currently active - if so, I can encode the 1 hour difference manually.

Thanks in advance for any help.

Chris.
Chris Lawton
Chris@Lawton.net

 

[bperry]

I think, as you have found out, SQL Server simply picks up
the date and time from the operating system, and does not know about daylight savings time.

In SQL 2000 there's a new function to return (I think) UTC time. The function is called GETUTCDATE(). Check it out in BOL....hopefully this will be all you need.


I think there's an API called GetTimeZoneInformation, so I guess you could write an extended stored procedure to call that and get some useful information.


Here's some code I found that provides the date and time in Greenwich Mean Time which is independent of daylight saving time. Perhaps you can use it or adapt it to your situation?
---------------------------------
declare @deltaGMT int
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'ActiveTimeBias', @DeltaGMT OUT
select getdate() as LocalTime, dateadd(minute, @deltaGMT, getdate() ) as GMT
---------------------------------

Here's some interesting software related to time, plus a few white papers re: time zone problems.

http://www.solution-soft.com/timemachine.shtml

----------------------------------------
That's as much as I know, which wasn't much. Hope it helped a bit.

brian perry

 

[clawton]

Thankyou Brian.

That information was just what I was looking for. The code sample should be fine to use asis.

Once again, thanks. It is much appreciated.
Chris Lawton
Chris@Lawton.net