In SQL Server, you can use the TODATETIMEOFFSET() function to return a datetimeoffset value that is translated from a datetime2 expression. It accepts two arguments; the date, and the offset that you’d like to apply to that date.

Below are examples of usage.

Syntax

The syntax of TODATETIMEOFFSET() goes like this:

TODATETIMEOFFSET ( expression , time_zone )

Where expression is an expression that resolves to a datetime2 value, and time_zone is the time zone you’d like to apply to that expression (date).

You can provide the time zone in minutes or hours. If you provide it in minutes, use an integer (e.g. -120), otherwise to provide it in hours, use a string (e.g. '+04.00'). The range for the time zone is +14 to -14 (hours).

Also, the date you provide is interpreted in local time for the specified time zone.

Example 1

Here’s a basic example of usage:

SELECT TODATETIMEOFFSET( '2112-01-01 00:00:00', '+08:00' ) AS Result;

Result:

Result
----------------------------------
2112-01-01 00:00:00.0000000 +08:00

Example 2 – Negative Value

This is the same as the previous example, except that I use a negative value instead:

SELECT TODATETIMEOFFSET( '2112-01-01 00:00:00', '-08:00' ) AS Result;

Result:

Result
----------------------------------
2112-01-01 00:00:00.0000000 -08:00

Example 3 – Using a datetime2 Variable

In this example, I explicitly set a variable with a datetime2 value, then apply TODATETIMEOFFSET() to that value, and compare the values:

DECLARE @date datetime2 = '2112-01-01 00:00:00.0000000';
SELECT 
    @date AS 'Original Date',
    TODATETIMEOFFSET( @date, '+08:00' ) AS '+08:00';

Result:

Original Date               +08:00
--------------------------- ----------------------------------
2112-01-01 00:00:00.0000000 2112-01-01 00:00:00.0000000 +08:00

As we can see with this example (and the previous ones), the original date value doesn’t have any datetime offset information (because it’s a datetime2 value), but once we run it through the TODATETIMEOFFSET() function, we end up with a datetimeoffset data type.

Example 4 – Using a datetimeoffset Variable

This is similar to the previous example, except that the original date is set as a datetimeoffset value:

DECLARE @date datetimeoffset = '2112-01-01 00:00:00.0000000 +04:00';
SELECT 
    @date AS 'Original Date',
    TODATETIMEOFFSET( @date, '+08:00' ) AS '+08:00';

Result:

Original Date                      +08:00
---------------------------------- ----------------------------------
2112-01-01 00:00:00.0000000 +04:00 2112-01-01 00:00:00.0000000 +08:00

Example 5 – Using the Current Date/Time

In this example, I pass in the SYSDATETIMEOFFSET() function as the date expression. This function generates the current date/time of the computer that’s running the SQL Server instance:

SELECT 
    SYSDATETIMEOFFSET() AS 'Current Date',
    TODATETIMEOFFSET( SYSDATETIMEOFFSET(), '+08:00' ) AS '+08:00';

Result:

Current Date                       +08:00
---------------------------------- ----------------------------------
2018-06-06 08:50:57.8382284 +10:00 2018-06-06 08:50:57.8382284 +08:00

Example 6 – Providing the Time Zone Offset as an Integer

As mentioned, you can also provide the time zone offset as an integer instead of a string:

SELECT TODATETIMEOFFSET( '2112-01-01 00:00:00', -180 ) AS Result;

Result:

Result
----------------------------------
2112-01-01 00:00:00.0000000 -03:00