At Time Zone

Rob Farley looks at the AT TIME ZONE operation in SQL Server 2016:

But despite the longwindedness of it, I love it, because at no point did I need to figure out that Adelaide was in +10:30, or that Eastern was -5:00 – I simply needed to know the time zone by name. Figuring out whether daylight saving should apply or not was handled for me.

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. This is a little frustrating, but it’s not SQL’s fault – we need to blame Windows for that. I guess the Windows registry doesn’t remember the hotfix that went around that year. (Note to self: I might need to ask someone in the Windows team to fix that…)

Like most companies dealing with multiple time zones, we ended up building a table and function to translate.  This is a nice, built-in way of doing something very similar.

Related Posts

Working With UTC And Local Times

Jo Douglass shows how to use the DATETIMEOFFSET data type and AT TIME ZONE syntax to convert between UTC and local times: Run select SysDateTimeOffset(); and you should see a date and time which mirrors your server’s current time, plus a time zone offset showing its current offset from UTC; this includes any time zone offset, plus […]

Read More

Annoying Date Formats

Randolph West shows that even The Best Date Format can deceive you under certain circumstances: Look carefully. DATE and DATETIME2 are showing the date of 12 July 2017 as expected. Unfortunately, the DATETIME and SMALLDATETIME data types are showing a date of 7 December 2017. That’s not good at all. It means that the ISO 8601 standard does not work the way we might expect it to. The […]

Read More

Categories

July 2016
MTWTFSS
« Jun Aug »
 123
45678910
11121314151617
18192021222324
25262728293031