By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,234 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,234 IT Pros & Developers. It's quick & easy.

Time Zone question

P: n/a
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.
Nov 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
> 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

--
http://www.aspfaq.com/
(Reverse address to reply.)
Nov 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.