Get startedGet started for free

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

View Course

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;
Edit and Run Code