Hi, I'm using an sql server 2005 hosted in the states. Soon we will move the db to Europe. I'd like to update the dates in the db to take into account the timezone difference. Is there a good way to do this? Or is it just a case of looping through the dates and adding the timezone offset?
+2
A:
Does it really need to change because it was physically moved? If you're supporting global applications I would store all my date/time information in GMT and translate to local on the client side.
Chris Ballance
2009-02-01 06:07:35
Interesting. thanks. The problem is that we already have data in the db. And once we move, the data will only be used in that timezone.
2009-02-01 06:12:42
All time data should be stored in universal time as a matter of principal. Then these issues never come up.
Steve
2009-02-01 06:26:20
+2
A:
I think you should be saving the date/times in database in a constant timezone it can be either GMT (because it is pretty simple to transform it into other timezones) or the current timezone of the server if it is not GMT.
Th eonly reason the later option makes sense is that you use the CURDATE function to insert/filter records from the database otherwise GMT is the best option.
You can try using DATEDIFF with DATEADD but due to DST this will not always give the accurate time (off by 1 hour in some cases).
There is a known limitation in SQL Server 2005 about timezone conversions, please refere to the following link for more information: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=308563