SQL Server TODATETIMEOFFSET Function

Summary: in this tutorial, you will learn how to use the SQL Server TODATETIMEOFFSET() function to interpret a DATETIME2 value in local time for a specified timezone.

Introduction to SQL Server TODATETIMEOFFSET() function

The TODATETIMEOFFSET() function translates a DATETIME2 value into a DATETIMEOFFSET value.

The following shows the syntax of the TODATETIMEOFFSET() function:

TODATETIMEOFFSET(expression,time_zone)
Code language: SQL (Structured Query Language) (sql)

The TODATETIMEOFFSET() function accepts two arguments:

• expression is an expression that can resolve to a DATETIME2 value.
• time_zone can be an integer or string. It is a time zone offset in minutes if you use an integer. Otherwise, it is an hour and minutes in the form '{+TZH:THM or '-TZH:THM', where TZH is the hour and THM is minute. The range of hour is from -14 to +14.

SQL Server TODATETIMEOFFSET() function examples

Let’s take some examples of using the TODATETIMEOFFSET() function.

A) Using SQL Server TODATETIMEOFFSET() function the change the time zone offset of a date and time example

This example changes the zone offset of a date and time to the zone -08:00:

SELECT
    TODATETIMEOFFSET (
        '2019-03-06 07:43:58',
        '-08:00'
    ) result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
----------------------------------
2019-03-06 07:43:58.0000000 -08:00

(1 row affected)Code language: CSS (css)

B) Using SQL Server TODATETIMEOFFSET() function to change the time zone offset in minutes

This example changes the current time zone to -180 minutes:

SELECT 
    TODATETIMEOFFSET (GETDATE(), -180) result;
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

result
----------------------------------
2019-03-06 08:34:32.087 -03:00

(1 row affected)Code language: CSS (css)

C) Using SQL Server TODATETIMEOFFSET() function to add a specified hour time zone offset to the current date and time

The following example adds a 12-hour time zone offset to the date and time '2019-03-06 09:55:00':

SELECT 
    TODATETIMEOFFSET (
        '2019-03-06 09:55:00', 
        '+13:00') 
    result;
Code language: SQL (Structured Query Language) (sql)

The output is:

result
----------------------------------
2019-03-06 09:55:00.0000000 +13:00

(1 row affected)Code language: CSS (css)

In this tutorial, you have learned how to use the SQL Server TODATETIMEOFFSET() to return a DATETIME2 value to a DATETIMEOFFSET value.