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 |
|
phdiwakar
Starting Member
15 Posts |
 Posted - 2007-11-01 : 15:40:46
|
| I have several stored procedures and UDFs that have code that convert between GMT timezone and other U.S. timezones.for example,to convert from GMT to CT I use this: @dtmCentral = dateadd(hour, -5,@GMTdtm)However, come Nov 4th after 2AM, when DST ends, I need to change this to @dtmCentral = dateadd(hour, -6,@GMTdtm)Assuming that I can store this -5 or -6 value in a global variable, I want to have a stored procedure that updates it when DST starts and ends.Is there a better way of doing this other than the option of going through the entire calculations of figuring out whether it's DST or not each time ?Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-01 : 15:47:14
|
| You should be storing this information in a timezone table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 15:48:37
|
| GetUTCDate() ?? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-01 : 15:51:11
|
| GETUTCDATE just gets you the UTC date and does not do conversions.Since we support international and domestic customers on the same system, we have our servers set to GMT. We then have a timezone table that contains the specific timezone details including DST information plus we have a UDF that does the conversion (reads from the timezone table).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
phdiwakar
Starting Member
15 Posts |
Posted - 2007-11-01 : 15:53:32
|
quote:
Originally posted by tkizer
You should be storing this information in a timezone table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
tkizer, Thanks for the suggestion, but I need to do this in several databases. I have 2 options in that case:1) Have a separate table for each database.2) Create only 1 table and access it from all databases, but this will slow down the UDF /Stored procedure considerably. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-01 : 15:56:51
|
| We store the timezone stuff in each database since the data and code will hardly ever change. It's only when a country (or whatever) changes DST or timezone laws that you'll need to make a change. This is very, very rare though.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 16:16:27
|
| SQL 2008 has some additional support in this area. No help now, I guess, but might be something you could plan for? |
|
|
|
|
|
|
|
|
Global Variable??