Thought Bubble #2 – Date and time offset

01_DateAndTimeOffset_ThoughtBubble

Do you know what the thought bubble returns? Do you know if the functions from the bubble do even exist in SQL Server?

In this article, we will concentrate on some interesting functions related to date and time values that have an offset.

There are many functions in SQL Server which we don’t use on a daily basis and from that reason, we often forget about them. Some of these functions are meant to be forgotten because they are very, very specific, but there are times when they can make our lives a lot easier.  The following functions can save us a lot of trouble in some situations.

There are two functions supported in SQL Server, related to the offset of a date:

  • SWITCHOFFSET
  • TODATETIMEOFFSET

Before I dive in the explanations for these functions, I want to make sure that everybody is on the same page with the knowledge about time zones and time offsets. The following definitions are taken from Wikipedia:

Time zone =  a region that observes a uniform standard time for legal, commercial, and social purposes. Time zones tend to follow the boundaries of countries and their subdivisions because it is convenient for areas in close commercial or other communication to keep the same time.

Time offset = an amount of time subtracted from or added to UTC to get the current civil time – whether it’s standard time or daylight saving time.

UTC offset = the difference in hours and minutes from Coordinated Universal Time (UTC) for a particular place and date. It is generally shown in the format ±[hh]:[mm], ±[hh][mm], or ±[hh]. So if the time being described is one hour ahead of UTC (such as the time in Berlin during the winter), the UTC offset would be “+01:00”, “+0100”, or simply “+01”.

So now to get back to SQL Server:

SWITCHOFFSET

[MSDN]: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

The syntax is the following:

and while the first parameter is very straight forward (a datetimeoffset data type), the second one is more interesting, because it can be of two different types:

  • a character string in the format [+|-]TZH:TZM: ‘+07:00’, ‘-02:00’
  • a signed integer (of minutes) that represents the time zone offset, and is assumed to be daylight-saving aware and adjusted: -120, +60

Q: What is interesting about the SWITCHOFFSET offset function?

A: When we use it, we see that not only the time offset is changed, but also the hour is switched and presented in the new offset. This is shown is the examples below.

(PS: MSDN: Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly. This is because the query optimizer is unable to obtain accurate cardinality estimates for the datetime value. To resolve this problem, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates and will produce a more efficient query plan.)

SWITCHOFFSET - examples - offset

 

TODATETIMEOFFSET

[MSDN]: Returns a datetimeoffset value that is translated from a datetime2 expression.

The syntax is the following:

TODATETIMEOFFSET ( expression , time_zone )

This function is used with a different purpose than the previous one: we give it two parameters, a normal date (without an offset) and then an offset and it will concatenate the two parameters and provide us with the DATETIMEOFFSET value. We can use it when we want to data that is not offset-aware, for example when we have the normal datetimes in a column in a table and the offsets in a different column.

Q: What is interesting about the DATETIMEOFFSET  offset function?

A: When we use it, we see that only the time offset is changed, the date remains the one provided in the first parameter. This is the main difference between TODATETIMEOFFSET and SWITCHOFFSET. This is illustrated in the example below:

TODATETIMEOFFSET - examples - offset

 

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 18 =