How to know the TimeZone StandardName or DayLightName from TimeZoneOffset in Sql Server

Question

I am using Sql Sever 2008 R2. Is there a way to identify the time zone Standard name or daylight name from timezoneoffSet.

For example I have "2013-09-26 03:00:00.0000000 -04:00" and need "Eastern Daylight Time" from above.

How can I accomplish this in SQL server ?

Any suggestions will be appreciated.

Answer 1

If you're talking about getting the local system's time zone, I've investigated that heavily in the past, and it isn't possible without resorting to either "unsafe" SQL CLR calls, or unsupported xp_regread calls to read this out of the registry. The general recommendation is to do that in application code, and not in the database.

However, if what you are saying is that you have an offset of -4:00 in your input value, and you want to translate that to a time zone name, I'm afraid that isn't possible at all, neither in SQL, nor in your application code.

The reason is that there are many time zones that share the same offset. For example, see this Wikipedia page that shows all of the zones that use -04:00.

Even if you limit the scope to just the United States, it still won't work in certain cases due to daylight saving time. For example, consider the time stamp of 2013-11-03T01:00:00-05:00. Is this Eastern Standard Time? Or Central Daylight Time? There's no way to tell, because at this moment it could be either one.

In the USA (unlike Europe), each time zone transitions at 2AM in its own local time. So it's like a wave that moves from the east to the west. While the US time zones are normally one hour spaced apart, during the spring-forward transition they can be two hours apart, and during the fall-back transition they can have the same exact local time.