Monday, January 6, 2014

Using T-SQL Convert LOCAL datetime to UTC datetime value using UTC offset (defined)

While working on some things recently, I figured there is an easy way to convert (using T-SQL) local time to UTC departure time … and that is leveraging built-in DATEADD function  

Reference

The key is using the right offset value for calculating UTC .. in the scenario that we dealt with, the database has Offset defined in minutes and is based on the offset/difference in minutes FROM UTC time so to convert the captured local date time to UTC.. the following works just fine ..

DATEADD(mi, - <UTCOffsetValue>, <LOCALDateCaptured>)    // mi - minutes

As an example for offset, Canada EST (with Daylight) has the value for the Offset defined as -300 [5 hours behind of UTC] whereas for France it is defined as + 60 [1 hour ahead of UTC]

No comments:

Post a Comment