Updated Answer
I wrote a utility that I talked about in the original answer, which you can find here .
In addition, with SQL Server 2016 (and Azure SQL Database), you can now use the AT TIME ZONE keyword to convert between time zones.
Original answer
Unfortunately, there is no great solution for working with time zones in SQL Server.
I have seriously researched your issue, as well as this one . There are no built-in time zone functions, and any use of TimeZoneInfo in SQLCLR requires the assembly to be registered as "unsafe". This is usually not required.
I also researched using Noda Time from SQLCLR. You can read about this in this issue . It must also be registered as "unsafe" due to some elements being cached internally.
Ultimately, with one element, the problem is that in SQLCLR there is no way to cache anything. You cannot use static variables in thread safe mode, and you cannot perform any kind of thread synchronization or use classes like ConcurrentDictionary . SQL wants full control over the stream model of the assembly. In "safe" builds, only a single-threaded once-and-draft style code works. I delved deeply into this question: Multithreaded caching in SQL CLR
Hopefully Noda Time will eventually be built, which will work in SQLCLR, but it will be a special build that does not do any caching. Thus, he will not work so fast, but he will do his job while he is safe.
TimeZoneInfo unlikely to change. Therefore, if the SQL Server command never performs the time zone function directly in SQL Server properly (for example, Oracle and Postgres), you have only a few options:
Do not try to convert time zones in a data layer. Work with datetime or datetime2 in UTC or use datetimeoffset with any offset. But do all the conversions between time zones at the application level. This is my best recommendation.
Copy all the data for time zones into real SQL tables and write the functions that work with this data. This is not a good idea, because data changes frequently, so table maintenance can be a problem. Likewise, getting features, including all daylight saving rules, can be tricky. I don’t know about any project that would be complete with a good and neat one, but if anyone is there then please let me know in the comments.
Enable xp_regread and work with time zone data directly from the Windows registry keys. Updates will be made for you, but you still have the same problems writing these features. And enabling registry reading can be the same security risk as enabling unsafe CLR assemblies.
Another idea that I am considering is to write a parser and IANA / Olson TZDB features specifically designed for SQL Server. This will be similar to option 2 above, but will be done using a convenient method and with standard IANA data instead of Windows time zones. Maybe I will get to this someday, or maybe someone will beat me. Again, I do not know about the current project that is doing this, but if someone knows about one, please let me know in the comments. ( done - see update above )
Regarding SWITCHOFFSET , this only works when you already know the target offset. This is half the battle, and probably why Microsoft still marks the datetimeoffset as not “daylight awareness” in the docs .