This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

You can now use AT TIME ZONE to access different time zones in SQL
If you're working with different time zones, the AT TIME ZONE keywords introduced in SQL Server 2016 will make your life much easier - this blog explains how they work.

Posted by Andy Brown on 25 November 2019

In this blog

The old way
The new way
Using variables for time zones

A new(ish) way to display time zones in SQL

A recent course delegate showed the other Wise Owl Andy (Gould) that there's now a new way to work across time zones.

Preston North End kick off at QPR at 15:00 on Saturday 7th December 2019, but I want to tell my friend in the eastern United States what this time will be for him (if you're reading this after the game, you'll already know the score ...).

Said friend lives in Winston-Salem, North Carolina - hi Jon! - which is on Eastern Standard Time.

The old way

Until the launch of SQL Server 2016, you would have had to do something like this:

-- get kick-off time

DECLARE @KickOffDateTime datetime = '2019-12-07 15:00:00'

-- get this time in the correct time zone

DECLARE @EasternStatesKickOff datetime = SWITCHOFFSET(

@KickOffDateTime,

'-05:00'

)

-- show this time, neatly formatted

SELECT

FORMAT(

@EasternStatesKickOff,

'yyyy-MM-dd HH:mm:ss'

) AS 'Formatted time'

What this does is to change the offset for the time given to -5 hours, since we know that the Eastern US time zone is 5 hours behind at the moment. But the question is - will it still be 5 hours behind next week?

The new way

From SQL Server 2016 onwards you can now link to the following registry hive (as Microsoft describe it):

Where to find time zones in the registry.

Fortunately, however, you don't need to do this, because you can instead just show the current time zones as returned by Windows, using the time_zone_info view:

-- list all the time zones

SELECT * from sys.time_zone_info

ORDER BY current_utc_offset

Here's what this returns on my laptop:

The first of the 139 time zones returned from the above query.

You can get at this information using the AT TIME ZONE keywords:

-- show equivalent time in Eastern US

SELECT CAST('2019-12-07 15:00:00' AS datetime)

AT TIME ZONE 'US Eastern Standard Time';

Notice that you have to convert the date time stored as a string into an explicit datetime format before finding out the time zone for it.

Here's what the above query would return:

The kick-off time in the US will be 5 hours behind, as we had suspected.

Using variables for time zones

You can store the names of time zones in variables. Here's an example which would show when two people should log on to watch the Preston game (one in the Eastern US and one in Tokyo):

-- get kick-off time

DECLARE @KickOffDateTime datetime = '2019-12-07 15:00:00'

-- set variables to hold the two time zones

DECLARE @TimeZoneUS varchar(100) = 'US Eastern Standard Time'

DECLARE @TimeZoneJapan varchar(100) = 'Tokyo Standard Time'

-- show equivalent time in Eastern US

SELECT

@KickOffDateTime AT TIME ZONE @TimeZoneUS AS 'US time',

@KickOffDateTime AT TIME ZONE @TimeZoneJapan AS 'Japan time'

This would give two times for comparison:

The Preston kick-off time in the US and Japan.

If you're interested in the speed of the new command, you can find a (for me too) detailed analysis at this blog.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

A new(ish) way to display time zones in SQL

The old way

The new way

Using variables for time zones

Head office

London

Manchester