| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-02 : 10:55:00
|
| Pankaj writes "How can I store the timezone information in SQL Server 2000's datetime column. the reason is if a timezone at server is changed then all the past data won't refelect correct time untilI store timezone inf. in separate column or store GMT/UTC in all my datetime columns ? is there any other way ?Thanks" |
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2002-05-02 : 11:16:47
|
| I would store GMT/UTC in all my datetime columns. You then need to use some sort of offset any time you want to display dates on the client. Passing an integer offset to the stored procedure may be the easiest, but there may be other ways to handle this on the client as well. |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-05-02 : 11:59:17
|
quote:
Passing an integer offset
In minutes, or all the Yaks in Katmandu will be after you! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-02 : 12:24:44
|
| Why minutes an not hours? I must be missing something because I thought all time zones were separated by One Hour.Michael |
|
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2002-05-02 : 12:30:18
|
quote:
In minutes, or all the Yaks in Katmandu will be after you!
declare @offset smallintselect @offset = -7 --Seattle, Washingtonselect dateadd(hh, @offset, getutcdate())I don't think the yaks will complain over this. I have never of heard of timezones varying on any increment other than hours. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-02 : 12:32:49
|
| JB, that is exactly what I'm doing.Does anyone see any issue with that way?Michael |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-05-02 : 12:36:53
|
take a look at this . . . notice the time in Kathmandu?quote:
Standard time zone: UTC/GMT +5:45 hours No daylight saving time at the moment
several other are on the 1/2 hour too . . . .<O>Edited by - Page47 on 05/02/2002 12:55:51 |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-05-02 : 12:40:32
|
| Wow, well you learn something every day :)Why are some places not exactly 1 hr differnt?Michael |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-02 : 12:48:41
|
| There are a number of timezones that differ by less than a full hour (not to mention timezones that don't observe daylight savings) You can take a look at this site:http://www.worldtimezone.com/daylight.htmThe way that Windows handles timezone offsets is to use the difference in minutes between UTC and the local time. If you search the Windows registry for "timezone" you'll get a list of all of the timezones available in the control panel, and if you look at the values used (need a little hex conversion though) you'll see how they do it.hmmmmm...that link seems to be broken. You can try searching Google for "timezone" and you'll find a number of sites that show all of them.Edited by - robvolk on 05/02/2002 12:51:23 |
|
|
|
|
Timezone