Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
In this blog
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.
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?
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.
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.