Can AT TIME ZONE return inaccurate results for data before 2004

sql servertimezone

SQL Server 2016 added the AT TIME ZONE operator. From the documentation:

AT TIME ZONE implementation relies on a Windows mechanism to convert datetime values across time zones.

AT TIME ZONE calls the mscorlib.ni!TimeZoneInfo.ConvertTime method according to ETW tracing against a simple query. Jonathan Kehayias has a blog post where he pulls all of the time zone rules from the System.TimeZoneInfo class. I can only find rules that take effect on 01/01/2004 or later in the output:

tz rules

Rob Farley mentions that in a blog post that a time zone rule change in the year 2000 does not appear to be respected by AT TIME ZONE:

It works by using the Windows registry, which has all that information in it, but sadly, it’s not perfect when looking back in time. Australia changed the dates in 2008, and the US changed its dates in 2005 – both countries saving daylight for more of the year. AT TIME ZONE understands this. But it doesn’t seem to appreciate that in Australia in the year 2000, thanks to the Sydney Olympics, Australia started daylight saving about two months earlier.

I feel that there is a large amount of circumstantial evidence that the AT TIME ZONE operator may return inaccurate results for dates earlier than the year 2004. However, I cannot find any documentation that AT TIME ZONE uses the System.TimeZoneInfo class, that AT TIME ZONE may be inaccurate for older dates, or that the System.TimeZoneInfo class may be inaccurate for older dates.

Is there a SQL Server product limitation that results in AT TIME ZONE returning inaccurate results before the year 2004?

Best Answer

I don't know if this is definitive, but I found this Q&A on Stack Overflow that includes an answer from a former Microsoft employee (emphasis mine):

Does .NET have the history of time zone changes?

.NET tracks some history, but it is not always accurate. You've stumbled upon one of the inaccuracies.

.NET imports all of it's time zone information from Windows via the registry, as described here and here. If you look in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\Russian Standard Time\Dynamic DST you'll find that it only tracks information from 2010 forward for this time zone. Testing dates in year 2000 is not going to work well, as it will fall back to the earliest rule available (2010).

So basically, SQL Server is calling into .NET, .NET is looking in the Windows registry, and the Windows registry isn't guaranteed to have complete history of time zone changes. As you've noticed, on modern versions of Windows 10, the oldest adjustment rules I'm seeing are from 2003.

If you need history older than that, you'll have to go outside of SQL Server / the built-in .NET Framework library code. I've heard good things about NodaTime (which gets its info from the IANA official time zone DB), so you could use this open-source solution that's built on top of some infrastructure from that project:

SQL Server Time Zone Support - GitHub

You could also roll your own solution if you're a bad person and hate open source (or you just want to or whatever).

Related Question