Unusual Date Time question

[thendrickson]

We have a Server in the eastern time zone. users access this server through the web

We also have sites around the country in different time zones.

I need to store datetime values based on the local time of the user, not the server time. Basically I do not need to be 100% acurate, just the hour part. And I know the site location in every case. We populate time_zone when we set up a new site

The only problem I am having is accounting for locations where daylight savings time is not used (such as Indiana)
Sometimes I need to subtract 1 from the server time and sometimes I do not (for that site)

Currently, I get the server time and adjust the local hour using this very simple sp

CREATE  Procedure dbo.LocalTime @site_code VARCHAR (10), @localtime datetime  Output 
as
Declare @t as tinyint

--first see what the time zone adjustment is for the sight

SELECT @t = (Select time_zone  from Site where sitecode = @site_code)

--if there is an value in the site table calculate it
if not  @t  is null
	SET @LocalTime = DATEADD(hh,-@t,GetDate())
Else -- otherwise use the server time
	set @localtime = GETDATE()

GO

So basically I need a way to determine when the server changes from standard time to daylight savings time.

Has anybody ever had to deal with something like this?

 

[traingamer]

You're in luck, Indiana starts using Daylight Savings Time this year.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SQLBill]

I don't know your schema and you didn't supply it....but, I suggest that you have a table:

tLocationTimeInfo

cLocation cTimeDiff cDST


You would save the site's name or location in cLocation, the +/- hours in cTimeDiff, and 1/0 (BIT) in cDST showing whether they use Daylight Savings Time or not. Then any query would just:
1. check the cDST for cLocation
2. if the bit is on (1) then,
adjust the time by one hour
3. if the bit is off (1) then,
don't adjust the time.
4. lastly, the cTimeDiff column would be used in queries to tell the time difference between two locations.

-SQLBill

Posting advice: FAQ481-4875

 

[gmmastros]

Alternatively...

You could include a little JScript (or vbscript) in your page to determine what the time offset is. You should then modify your queries to return the time in GMT time. Then, on the front end, add or subtract hours as needed.

Here's an article that may help a little.

http://classicasp.aspfaq.com/date-t...-do-i-convert-local-time-to-utc-gmt-time.html

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[thendrickson]

As always, You guys were a big help.

The article George provided had the piece I could not recall.

Once I know the day for the change it is easy

SQLBill I have a table of site specific data that contains a column of an integer value that provides the + or - from our server time. I just inherited this problem and really have not had time to think it out clearly My plan was to use the state field to determine if they observed daylight savings time. But the bit column makes more sense.

Greg, You may have made everything moot. But the way thing so, I had best account for daylight savings time anyway. Also, we may go international at any time.

As far as GMT goes. I lost that argument 8 months ago I am afraid.

Of course I was arguing in favor of the 24 hour clock and ignoring time zones totally at the time