5

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.

6

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.

1
  • I was afraid of this (having to replicate the Windows feature that AT TIME ZONE supplies). Unfortunately, there is no way to get around the server time conversion issue even if the datetimeoffsets are all UTC or all one time zones. Because the analysis is of bucketized records related to server load, it needs to be based on "human-time" in the end (i.e. 9:00AM-9:05AM user load windows, regardless of time of year), and unfortunately humans don't stick to one time zone.
    – Cade Roux
    Jul 1 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.