1

My server is in California, and ATM returns time with a PDT offset: -07:00. I can convert this to UTC easily enough:

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00')

However, what I really want is Western European Time (AKA London Time), which at the moment is +00:00, but in a couple weeks will be +01:00. I'd rather not build in the logic for when different parts of the world enter and leave daylight saving time.

MS SQL doesn't support time zone names as a parameter in SWITCHOFFSET(). Is there some trick to getting a "real world" time zone, as opposed to merely an offset? This topic has been surprisingly difficult to Google; there are a zillion articles about how DATETIMEOFFSET is now available, but very little about how to take advantage of it.

Here's the MS article on SWITCHOFFSET(). I see nothing about getting to actual local time zones.

There's a pretty good question about this for .NET here, but I'd like to do this without setting up CLR.

Community
  • 1
  • 1
  • I don't know of any way to make SQL Server aware of daylight savings time. In previous solutions I've always created a calendar table. The nice thing about this is that you can update the calendar table long before Windows or SQL would ever get the updates for time zones... – Aaron Bertrand Mar 15 '12 at 16:53
  • Even if the time zone names were supported, you would still have to figure out when to use which name, because DS-names and non-DS-names usually differ. Also, some time zones have identical names. [Here's one example](http://www.worldtimezone.com/wtz-names/wtz-cst.html). – Andriy M Mar 15 '12 at 18:52
  • @AndriyM: One can certain identify "Central Time" meaning either standard or daylight saving time, as appropriate. Exactly how you label the zones is an implementation detail. Regarding CST, those are not identical names, they are multiple uses of the same abbreviation. It makes a great example of why abbreviations should be used cautiously. –  Mar 15 '12 at 19:03

0 Answers0