In SQL Server, the TODATETIMEOFFSET() function was specifically designed to return a datetimeoffset value from a datetime2 value.

Given the fact that the datetime2 data type doesn’t actually support time zone offsets, and datetimeoffset must contain the offset, the TODATETIMEOFFSET() function allows you to specify a time zone offset to use.

This article provides some examples to demonstrate.

Basic Usage

Here’s an example of typical usage of the TODATETIMEOFFSET() function.

DECLARE @dt datetime2 = '2020-12-20 17:33:59.8900000';
SELECT
  @dt AS [datetime2],
  TODATETIMEOFFSET(@dt, '+07:00') AS [datetimeoffset];

Result (using vertical output):

datetime2      | 2020-12-20 17:33:59.8900000
datetimeoffset | 2020-12-20 17:33:59.8900000 +07:00

In this case I return the original datetime2 value and the resulting datetimeoffset value.

The result is that the original date/time is the same, except the specified time zone offset has been appended.

The range is +14 to -14 (in hours). The expression is interpreted in local time for the specified time zone.

Specify the Time Zone Offset in Minutes

You can alternatively specify the time zone offset in minutes. Here’s an example.

DECLARE @dt datetime2 = '2020-12-20 17:33:59.8900000';
SELECT
  @dt AS [datetime2],
  TODATETIMEOFFSET(@dt, +120) AS [datetimeoffset];

Result (using vertical output):

datetime2      | 2020-12-20 17:33:59.8900000
datetimeoffset | 2020-12-20 17:33:59.8900000 +02:00

In this case I specified +120 minutes, which is the same as two hours. So the result was a time zone offset of +02:00 (meaning plus two hours).

Also note that when you provide the time zone offset in minutes, you provide it as an integer value. On the other hand, when you provide it in hours, you need to specify it as a string.

See Convert a Date to Another Time Zone for examples on how to do this.