How to convert a DateTime to a DATETIMEOFFSET offset using SQL server’s AT TIME ZONE function.
Aahhh… dates and times in code.. aren’t they always fun? I needed to migrate from an older system which stored a date as a DATETIME to one that stored the column as a DATETIMEOFFSET, you could just use the TODATETIMEOFFSET() function but this won’t take into account daylight savings, when the offset changes, it just appends the offset you give it to the datetime. So how do we solve this problem? Well SQL server 2016 introduced this handy new AT TIME ZONE function. This function will take into account the date and give you the proper offset. So how do we accomplish this?
Example At Time Zone Function
SELECT '2019-07-17 14:15:00.000' AS OriginalDateTime,
CAST('2019-07-17 14:15:00.000' AS DATETIME) AT TIME ZONE 'Eastern Standard Time' AS WithOffset
I set the time zone here as Eastern because I know that my datetimes were stored in the system’s time zone which is Eastern. If you don’t know the original time zone the DateTime was stored in you are a bit out of luck here. There would be no reliable way for you to figure out the time zone of that datetime unless you have some reference in your database that would tell you.
The At Time Zone will treat the DateTime you give it as being in the Time zone you specified in the function and convert it to a DateTimeOffset datatype, now you have your offset correctly set.
The best part about this function is that it sets the correct offset based on the date. Something we could not do easily before. Let us prove this by using a Date we know was during DST
SELECT '2019-01-17 14:15:00.000' AS OriginalDateTime
This DateTime stored in DB , I know these were all stored using the Servers SysDatetime which is Eastern time.
CAST('2019-01-17 14:15:00.000' AS DATETIME) AT TIME ZONE 'Eastern Standard Time' AS WithOffset
Conclusion
And there we have it, a way that makes it just a bit easier to deal with the headaches that come with dates, times, time zones and daylight savings. Another great way to just avoid this all together if you can, is to just store everything in UTC and keep track of what time zone you would need to convert it to (if you need that info). This is especially important when your servers are hosted by a cloud provider. It’s possible that the actual local time of the server may not always be the same, in which case I think storing UTC is a must. How do you store your dates and times? Datetime offsets, UTC, local time of the server? Have you used the At Time Zone function? How was your experience?