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

Using DATEADD In SQL Server

Randolph West continues a series on date and time types in SQL Server with the DATEADD function: As with similar functions, DATEADD can do arithmetic on dates as well as times. The syntax is straightforward: DATEADD (datepart, number, date) The number portion must be an integer, and it must be within the acceptable range of values for the date part. […]

Read More

Working With Dates And Times In T-SQL

Tomaz Kastrun walks us through various functions to work with dates and times in T-SQL: Manipulating date and time in T-SQL is a daily and very common task that every DBA, SQL Developer, BI Developer and data scientist will come across. And over the years, I have accumulated many of the simple date or/and time […]

Read More

Categories

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