question

Simon Ho avatar image
Simon Ho asked

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

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

1 Answer

·
TG avatar image
TG answered

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

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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