Question on timezone and DST in T/SQL

Question

question

Simon Ho asked Dec 29, '09

Question on timezone and DST in T/SQL

I would like to do timezone conversion (with daylight saving adjustment) from UTC time to local timezone. Since I am working in a global system, I need to convert the time to different local timezone according to the regions the record belonging to. So, I wonder if there is any built-in function in T/SQL or SQL Server to do the timezone conversion on datetime field (to any given timezone region, with considering daylight saving adjustment).

Or, if built-in function is not available, any other suggestion? (since I need to cater conversion in many different regions and still need to consider the DST, and potential changes in DST from time to time)

Thanks a lot.

Simon.

t-sql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-30T09:53:42Z

TG answered Dec 30, '09

If the Sql Server machine is in the same timezone as the calling client then it would be easy:

select dateadd(hour, datediff(hour, getUTCDate(), getDate()), yourUTCDateValue)

However, if the calling client is not in the same timezone as the sql server machine then you need to either send the offset to sql server and perform the adjustment there, or do the offset adjustment at the client.

But depending on your technology the timezone adjustments may be performed during serialization/deserialization of the data. I believe that web services using SOAP may apply timezone adjustments to datetime values between the web service and the client so see if that is happening and account for that.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question