Time Zone question

[Rob T]

This is just a general question....trying to collect some ideas on how to
approach this issue:

I have a web app that has users across the country. Since they login, I can
make their profiles have a time zone field, so that's no big deal.

My data in question is stored in SQL 2000 in a datetime type and the server
is set to Eastern time. My first question is: Is the date stored on the
server stored as something like GMT format or does it get stored as the
eastern time? If it's stored in Eastern Time, I would assume I have to use
the Transact SQL function GetUTCDate() to convert it to GMT....then
reconvert it in my app to the users time zone?

I also have users in Indiana, where the time zones never "spring ahead/fall
behind" so I need to make sure their dates don't adjust when the east coast
does. Is there a common function I could use to do this mess for me...so I
don't have to keep track of the calendar date when daylight savings
starts/ends?

I'm also pondering how I should handle some of the reporting features; for
example. If the main office is headquartered on the east cost, their normal
business day would be from 12:00am to 11:59:59pm. so if they ran a sales
report for that day, it would be fine. However if a west coaster ran a
report for the day's sales, should I make sure their report's time is set to
Eastern...otherwise it would try to capture data from 9:00pm to 11:59:59pm,
thus skewing the report....

Thanks for your feedback.

-- Rob T.

 

[Aaron [SQL Server MVP]]

My data in question is stored in SQL 2000 in a datetime type and the
server

is set to Eastern time. My first question is: Is the date stored on the
server stored as something like GMT format or does it get stored as the
eastern time?

It is stored as whatever you inserted. If you put in 12:07 PM, it is 12:07
PM. The database doesn't know if you consider that to be GMT, or EST, etc.

If it's stored in Eastern Time, I would assume I have to use
the Transact SQL function GetUTCDate() to convert it to GMT

GETUTCDATE() returns GETDATE() +- the *current* UTC offset. Which is not
necessarily the same as what it would yield for April 12th of this year or
November 14th of last year. See the problem?

should I make sure their report's time is set to
Eastern...

That really depends on the business requirements. Are they only querying
for sales that *happened* in their time zone? What do the users expect?

otherwise it would try to capture data from 9:00pm to 11:59:59pm,

Why wouldn't it be >= 9:00 PM on day x and < 9:00 AM on day (x+1)?

To deal with UTC I usually have a calendar table with a UTCOffset column;
you can probably do something like this with a column for the offset in each
time zone you plan on supporting.

http://www.aspfaq.com/2519
http://www.aspfaq.com/2218