Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tomjacob
Starting Member
8 Posts |
 Posted - 2002-02-21 : 16:59:26
|
| Hi folks,I have an application where I have to store the values in GMT and to display it in CT. CT is 6 hours behind GMT now and after april 7 this year, because of the daylight time savings rule, CT will be 5 hours behind GMT.Right now what I am doing is using dateadd(hh,-6,GMTDateStoredInTable)That '-6' above is hard coded in my sp now and that shound not be the way. I should get that value in a variable and should be able to use that variable in ALL sps and DTS my application has.So my question is, Is there anyway I can declare and assingn Global variables (visibility in full sqlDatabae) and can MANUALLY change it often ?The alternate way is, have a table which stores the day light time changes for some 10-50 years and have another query which will compare the getdate() and the DB value and return 5 or 6 accorindingly. But I need to get this information on a query where 600*30 people using in an hour. So if possible I want to avoid that andHAVE A GLOBAL VARIABLE WHICH I CAN UPDATE MANUALLY WHEN THE DAYLIGHT TIME CHANGES EVERY YEAR. Eventhough it is not a fool proof way, performance wise, it wud compromise there.Please advise. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-21 : 17:09:07
|
| I don't know if this is an option, but JavaScript (and Java) have a Date object that makes it very easy to compare date values in different timezones, and it automatically adjusts for your timezone. Here's a link:http://msdn.microsoft.com/library/en-us/script56/html/js56jsobjdate.aspThere's a method that lets you create a date value, then use getTimezoneOffset to find the difference between local and UTC time (UTC is NEVER affected by daylight savings, but GMT may), so you'll automatically adjust for dates affected by daylight savings.This is something that obviously will work better on your client side, not on the SQL Server side. You can convert the date to UTC and store it in SQL Server, then retrieve it to the client and convert it back to local time. |
 |
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2002-02-21 : 17:09:16
|
| What I currently do is create a table that stores constants. I also have a UDF which returns the constant value by name.The end result in your sproc would bedateadd(hh,dbo.fnc_GetConstant('Offset'),GMTDateStoredInTable) |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-02-21 : 17:11:11
|
quote:
What I currently do is create a table that stores constants. I also have a UDF which returns the constant value by name.The end result in your sproc would bedateadd(hh,dbo.fnc_GetConstant('Offset'),GMTDateStoredInTable)
I really like this solution. Great use of UDF's!===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-02-21 : 17:20:31
|
| Maybe I am missing something, but AFAIK GMT and UTC are one and the same. Assuming that is correct (and I believe it is), and as long as your server is on Central Time, you can always calculate the adjustment factor by duing a datediff between your local time (GETDATE()) and UTC time (GETUTCDATE()) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-21 : 17:34:37
|
| The only reason I said that was because I read this in Joe Celko's SQL for Smarties:"Technically, Universal Coordinated Time (UTC) is not quite the same thing as Greenwich Mean Time. GMT is a 24-hour astronomical time system based on the local time at Greenwich, England. GMT can be considered equivalent to Universal Coordinated Time when fractions of a second are not important."And for some reason I remember reading something that suggested GMT may or may not follow daylight savings adjustments, either now or in the past, and that UTC never adjusted for it. Can't find it now however...maybe I imagined it! |
|
|
|
Lou
Yak Posting Veteran
59 Posts |
Posted - 2002-02-22 : 00:28:13
|
Daylight savings time has been the bane of my existence. I once had to do a query involving all the airports in North and South America. In the US not every one practices DST. In South America and the Carribean, some do some don't. Some do it on different dates. Others practice DST on different dates on different years. Uggh...  |
|
|
|
|
|
|
|
|
Global varaible in SQL server