COVID-19: Choose between our familiar (but now socially distanced) classroom training courses and our excellent new live online courses.
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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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

Time zone registry hive

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:

139 time zones

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:

Preston kick-off in US

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:

Two different times

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.

This blog has 0 threads Add post