I am utilizing an sql server 2005 located in the usa. Soon we'll slowly move the db to Europe. Let me update the dates within the db to consider the timezone difference. Can there be a great way to do that? Or perhaps is it simply a situation of looping with the dates and adding the timezone offset?
Will it really should change since it was physically moved? If you are supporting global programs I'd store my date/time information in GMT and translate to local around the client side.
I believe you ought to be saving the date/occasions in database inside a constant timezone it may be either GMT (since it is really quite simple to change it into other timezones) or even the current timezone from the server if it's not GMT.
Th eonly reason the later option is sensible is you make use of the CURDATE function to place/filter records in the database otherwise GMT is the greatest option.
You can test using DATEDIFF with DATEADD but because of DST this can not necessarily provide the accurate time (off by one hour in some instances).
There's a recognized limitation in SQL Server 2005 about timezone conversions, please refere towards the link that follows to learn more: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=308563