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

Getting The Current Date And Time In SQL Server

Randolph West shows a few functions which can retrieve current date and time information: What do we mean by local date and time? As discussed previously, SQL Server is not time zone aware, nor does it have to be. This is because the operating system that SQL Server runs on can have multiple custom regional settings […]

Read More

DateTime Arithmetic

Claudio Silva points out a difference between the DATETIME and DATETIME2 data types: I’m currently working on a SQL code migration from Firebird to SQL Server and I hit an error that I haven’t seen for some time. The error message is the following: Msg 206, Level 16, State 2, Line 4 Operand type clash: datetime2 is […]

Read More

Categories

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