7

A database / table I'm working it saves all its date values as datetime (without a timezone).

How can I find out which timezone that date was in?

For instance, the Central European Summer Time (+02) starts at 29th of March 2015 and the previous Winter Time (+01) started at 26th of October 2014.

Let's say I'm seeing a date of 2014-11-06, is there a function I can use to check to which timezone it belongs?

NB I know that there will be edge cases for when the time zone changes

I'm happy with some big CASE WHEN statement if that what it takes, but of course rather use some system function.

Improve this question
2
  • 1
    I would suggest using calandar table (auxiliary table) or best is use UTC and leave the localization of time to the app.
    – Kin Shah
    Mar 10, 2015 at 12:14
  • 1
    The basic answer is: No, there is no way to tell from a random DateTime what timezone it is. If you NEED timezones, use DateTimeOffset: msdn.microsoft.com/en-us/library/bb630289.aspx
    – Sean Gallardy
    Mar 10, 2015 at 13:55

1 Answer 1

Reset to default
6

This is neither technically nor even conceptually possible, due to the nature of what timezones are.

It should be noted that timezones are not simply the offset from UTC / GMT (i.e. the "+" or "-" value). The "offset" is a property of the timezone, but it does not, and cannot, determine the timezone since:

  • several timezones can share the same offset
  • timezones can come and go and can even shift their offset
  • different regions can change their timezones

Timezones are determined by governments. For example, in the U.S.A., there are primarily 4 timezones for the contiguous 48 states. But those same offsets are also used by other regions that probably have different laws regarding when Daylight Savings starts and ends, or if they even have a Daylight Savings period. And in fact, there are two extra timezones for the contiguous 48 states to handle differences for 2 states: one for Indiana and one for Arizona.

Daylight Savings start and end times change over time. The Indiana and Arizona links above discuss this for those two states. In the United States, the DST start and end dates changed in 2007, but this didn't affect other regions using those same offsets. And the DST offset isn't always 1 hour (mentioned in the prior link).

So, with:

  • a DATETIME value alone you have no basis for determining either the Offset or the Timezone (even if you had a Timezone database)

  • a DATETIME value and an OFFSET--whether stored separately or combined in a DATETIMEOFFSET datatype--you have the OFFSET (obviously) but cannot determine the exact Timezone, though it could be narrowed down to a few, but only if you had a table of which Timezones participated in that Offset at that particular moment in time (see below for sources of such data). With only the Offset, the DATETIME value can be converted to UTC, but not to another Offset without first having a Timezone database and knowing which specific Timezone you are converting it to, which will indicate the Offset for that particular point in time. (By saying it cannot be converted to another Offset, I mean in any meaningful way; clearly the Offset value can be adjusted, but that doesn't help much if you don't know what to adjust it to)

  • a DATETIME value and a Timezone (name) then you can determine the Offset by looking up what the Offset was at that point in time (see below for sources of such data)

Unfortunately this Timezone data is not built into SQL Server. But you can get it from the following sources (and possibly others):

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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