This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
SQL Server 2012 equivalent of AT TIME ZONE capability
Question
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.
asked 2021-06-30 by Cade Roux
Answer
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.
answered 2021-06-30 by Andy Mallon