SQL Server 2012 equivalent of AT TIME ZONE capability

All we need is an easy explanation of the problem, so here it is.

Is there a way in SQL Server 2012 to get what the server time zone would be on a particular date/time? In SQL Server 2016, you have AT TIME ZONE, but that’s not available in 2012. I have a datetimeoffset and I need to convert it to the server’s timezone (on that date) before truncating it to datetime. SWITCHOFFSET only works if you know the offset, but for servers that change their timezone twice yearly, the offset will flip back and forth.

This query illustrates the capability in action in SQL Server 2016:

SELECT @@SERVERNAME AS [SERVERNAME]
    , GETDATE() AT TIME ZONE 'Central Standard Time' AS [GETDATE() AT TIME ZONE 'Central Standard Time']
    , GETDATE() AT TIME ZONE 'Eastern Standard Time' AS [GETDATE() AT TIME ZONE 'Eastern Standard Time']
    , DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Central Standard Time' AS [DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Central Standard Time']
    , DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Eastern Standard Time' AS [DATEADD(MONTH, -6, GETDATE()) AT TIME ZONE 'Eastern Standard Time']
;

In June at least, the tzoffsets in the first two columns differ from the last two columns because that "standard" time zone has a different offset in December than June.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This is a great use case for a Calendar table.

Aaron Bertrand covers this exact scenario in depth here. The entire article is quite in depth to replicate in an answer here, but I’ll try to summarize.

In essence, you create a table of dates, and include in the metadata the start/end of Daylight Saving Time for all the years you’re concerned about. Then, you can use those dates to convert time from your time zone to UTC or vice versa. Once you have a calendar table to join to, it suddenly becomes trivial to determine if you’re in Standard or Daylight time without needing to jump through hoops.

In your use case, you can convert to UTC trivially via the offset, then use Aaron’s calendar table approach, and accompanying function to convert back to local time.

As a matter of style, I’m also a big fan of always storing datetime values at UTC for canonical data storage. These time zone and daylight saving time conversations are complicated enough that if you always store the data in UTC, you can ensure you have good data, and only have to struggle with display bugs. If you have a bug in how you stored the data, fixing that requires retroactively fixing data, which is much more complex.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply