1. Working with offsets
One of the trickier parts in handling dates is dealing with offsets. The classic date and time data types like DATETIME and DATETIME2 have no in-built concept of time zone, so a date which reads 3 PM might be in UTC or it might be in some other time zone.
2. Anatomy of a DATETIMEOFFSET
In cases where knowing the time zone is important, SQL Server has another date type: the DATETIMEOFFSET. The DATETIMEOFFSET type is made up of three components: a date, a time, and a UTC offset. All of this takes up 10 bytes of space compared to 8 bytes for a DATETIME or 6-8 bytes (depending on the level of time precision) for a DATETIME2.
3. Anatomy of a DATETIMEOFFSET
SQL Server will then display the output string with all three components separated by spaces.
4. Changing offsets
The SWITCHOFFSET() function allows us to change the time zone of a given input string. If you pass in a DATETIME or DATETIME2, SWITCHOFFSET() assumes you are in UTC. You can also pass in a DATETIMEOFFSET to move from one known time zone to another.
Here we have the date from the prior slide. This date is in a time zone which is 4 hours slower than UTC, specifically, Eastern Daylight Time. If we want to change this to Los Angeles, California time, we will need to know that Los Angeles was in Pacific Daylight Time on April 10th, 2019. Pacific Daylight Time is UTC -7.
This result tells us what time it was in Los Angeles when it was 12:59 PM in the eastern United States: it was 9:59 AM.
5. Converting to DATETIMEOFFSET
We can certainly use the SWITCHOFFSET() function to convert a UTC-based time from a DATETIME2 data type into a DATETIMEOFFSET but there is an easier way if we just need to give the date and time an offset from UTC, and that is the TODATETIMEOFFSET() function.
This function takes two parameters: an input date and a time zone. From there, it generates a DATETIMEOFFSET.
Here, I have the date that I know is in Eastern Daylight Time but was stored as a DATETIME2 data type. Now I have it as a DATETIMEOFFSET, which will allow me to compare dates and times from other machines which might use other time zones.
6. Time zone swaps with TODATETIMEOFFSET
TODATETIMEOFFSET() can help us in slightly more complicated scenarios as well.
For example, suppose we have some date. We know the server saves data in Central Daylight time, or UTC -5. We want to create a DATETIMEOFFSET, but change the offset to align with our corporate headquarters in Bonn, Germany. In September of 2016, Germany was observing Central European Summer Time, or UTC +2.
We can use TODATETIMEOFFSET() to make this move but it requires two steps. First, we need to add 7 hours to our input date because there is a 7-hour difference between UTC -5 and UTC +2. Now that we have the right time, we can tell SQL Server that the date has an offset of plus 2 hours.
This gives us a result of 9:28 AM in Central European Summer Time.
7. Discovering time zones
For SWITCHOFFSET() and TODATETIMEOFFSET(), you need to know the offset number.
If you don't know that, you can look it up using a Dynamic Management View called sys-dot-time_zone_info.
This returns time zones and current UTC offsets, making it great for current searches but less great when working with historical data, as there is no way to search for time zones on a particular date.
8. Let's practice!
Now that we've learned a bit about offsets, let's put it to good use.