Pure SQL solution to convert historical DateTime values to DateTimeOffset?

Question

I have a large table with DateTime values that need to be converted to DateTimeOffset.

I can do this by using the following statement, which retains the date/time and adds the current time zone offset.

TODATETIMEOFFSET([StatisticDateUTC], DATENAME(tz, SYSDATETIMEOFFSET()))  

The problem is some of these values represent dates years ago, some in daylight savings, some not, so it's actually incorrect to put the current offset in all of them. Some of them should have an offset of -700 some should have an offset of -800.

If the time zone was consistent for all of the values, how can I get the correct offset? I know how this can be done in .net, as there are a nice set of functions to do it, but I need a pure sql solution, no CLR functions.

Answer 1

If you can upgrade to SQL Server 2016, or use Azure SQL Database (v12), then you can use the new AT TIME ZONE function, which is very similar to the TimeZoneInfo.ConvertTime method you may be used to in .NET.

Otherwise, consider a third-party solution, such as my SQL Server Time Zone Support package, which uses standard IANA time zones.

More on both in this related answer.