Converting to a date offset
In addition to SWITCHOFFSET(), we can use the TODATETIMEOFFSET() to turn an existing date into a date type with an offset. If our starting time is in UTC, we will need to correct for time zone and then append an offset. To correct for the time zone, we can add or subtract hours (and minutes) manually.
Closing ceremonies for the 2016 Summer Olympics in Rio de Janeiro began at 11 PM UTC on August 21st, 2016. Starting with a string containing that date and time, we can see what time that was in other locales. For the time in Phoenix, Arizona, you know that they observe Mountain Standard Time, which is UTC -7 year-round. The island chain of Tuvalu has its own time which is 12 hours ahead of UTC.
This exercise is part of the course
Time Series Analysis in SQL Server
Exercise instructions
- Fill in the time in Phoenix, Arizona, which, being Mountain Standard Time, was UTC -07:00.
- Fill in the time for Tuvalu, which is 12 hours ahead of UTC.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
DECLARE
@OlympicsClosingUTC DATETIME2(0) = '2016-08-21 23:00:00';
SELECT
-- Fill in 7 hours back and a '-07:00' offset
___(DATEADD(HOUR, -___, @OlympicsClosingUTC), '___') AS PhoenixTime,
-- Fill in 12 hours forward and a '+12:00' offset.
___(DATEADD(HOUR, ___, @OlympicsClosingUTC), '___') AS TuvaluTime;