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:
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?