This article contains examples of converting a datetime2 value to a datetimeoffset value in SQL Server.

When you convert a datetime2 value to datetimeoffset, the resulting value will depend on the fractional seconds precision that’s assigned to each data type, as well as any time zone offset you specify.

Both data types allow you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, the default scale of 7 is used.

The datetimeoffset data type includes a time zone offset and can preserve any offsets in the original value. However, datetime2 has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset defaults to +00:00.

The TODATETIMEOFFSET() function was specifically designed to convert a date/time value to datetimeoffset and add a time zone offset. However, see my comments (and examples) below regarding this option.

Example 1 – Implicit Conversion

First, here’s an example of an implicit conversion between datetime2 and datetimeoffset.

DECLARE 
  @thedatetime2 datetime2(7), 
  @thedatetimeoffset datetimeoffset(7);
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SET @thedatetimeoffset = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetimeoffset AS 'datetimeoffset(7)';

Result:

+-----------------------------+------------------------------------+
| datetime2                   | datetimeoffset(7)                  |
|-----------------------------+------------------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.1234567 +00:00 |
+-----------------------------+------------------------------------+

This is an implicit conversion because we’re not using a conversion function (like the ones below) to explicitly convert it. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the datetime2 value to a datetimeoffset variable.

We can see that the datetimeoffset variable is able to use the same precision as the datetime2 value (7 decimal places). We also end up with a time zone offset of +00:00.

Using 7 fractional seconds precision causes datetimeoffset to use 11 bytes for storage (10 for the data and 1 byte for the precision). The datetime2 type uses 9 bytes (8 for the data and 1 byte for the precision) when using a scale of 7.

However, you can reduce the precision by replacing the 7 with a lower number.

Example 2 – Rounding

If datetimeoffset has a lower precision than the datetime2 value, it will be rounded up.

Here’s an example:

DECLARE 
  @thedatetime2 datetime2(7), 
  @thedatetimeoffset datetimeoffset(6);
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SET @thedatetimeoffset = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2(7)',
  @thedatetimeoffset AS 'datetimeoffset(6)';

Result:

+-----------------------------+------------------------------------+
| datetime2(7)                | datetimeoffset(6)                  |
|-----------------------------+------------------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.1234570 +00:00 |
+-----------------------------+------------------------------------+

In this example, the datetime2 value has a scale of 7 but the datetimeoffset value’s scale is only 6. Therefore, its precision is only to 6 decimal places, and its sixth digit gets rounded up to 7 (instead of 6).

Example 3 – Explicit Conversion using CAST()

Here’s an example of an explicit conversion. In this case, I use the CAST() function directly within the SELECT statement to explicitly convert between datetime2 and datetimeoffset.

DECLARE @thedatetime2 datetime2;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SELECT 
  @thedatetime2 AS 'datetime2',
  CAST(@thedatetime2 AS datetimeoffset) AS 'datetimeoffset';

Result:

+-----------------------------+------------------------------------+
| datetime2                   | datetimeoffset                     |
|-----------------------------+------------------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.1234567 +00:00 |
+-----------------------------+------------------------------------+

Example 4 – Explicit Conversion using CONVERT()

Here’s an example of an explicit conversion using the CONVERT() function instead of CAST().

DECLARE @thedatetime2 datetime2;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SELECT 
  @thedatetime2 AS 'datetime2',
  CONVERT(datetimeoffset, @thedatetime2) AS 'datetimeoffset';

Result:

+-----------------------------+------------------------------------+
| datetime2                   | datetimeoffset                     |
|-----------------------------+------------------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.1234567 +00:00 |
+-----------------------------+------------------------------------+

Example 5 – Changing the Time Zone Offset

If you’re converting from datetime2 to datetimeoffset, you’re probably doing it for the timezone offset. It’s also highly likely that you’ll need a value other than the default +00:00.

Fortunately, you can use the TODATETIMEOFFSET() function to change the offset.

You can also use this function to convert the original datetime2 value to a datetimeoffset value. This function accepts any date/time value that can resolve to a datetime2 value, and an offset value.

Here’s an example:

DECLARE @thedatetime2 datetime2, @thedatetimeoffset datetimeoffset;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SET @thedatetimeoffset = TODATETIMEOFFSET(@thedatetime2, '+07:00');
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetimeoffset AS 'datetimeoffset';

Result:

+-----------------------------+------------------------------------+
| datetime2                   | datetimeoffset                     |
|-----------------------------+------------------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.1234567 +07:00 |
+-----------------------------+------------------------------------+

And here’s an example using the function within the SELECT statement:

DECLARE @thedatetime2 datetime2 = '2025-05-21 10:15:30.1234567';
SELECT 
  @thedatetime2 AS 'datetime2',
  TODATETIMEOFFSET(@thedatetime2, '+07:00') AS 'datetimeoffset';

Result:

+-----------------------------+------------------------------------+
| datetime2                   | datetimeoffset                     |
|-----------------------------+------------------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.1234567 +07:00 |
+-----------------------------+------------------------------------+

One important point about the TODATETIMEOFFSET() function is that it uses the same fractional precision as the date/time argument passed to it.

Therefore, if your datetime2 uses a lower precision than the datetimeoffset, you can always reassign it to a variable with a higher precision, then pass that converted value to TODATETIMEOFFSET().

Example:

DECLARE @lowprecision datetime2(3), @highprecision datetime2(7);
SET @lowprecision = '2025-05-21 10:15:30.123';
SET @highprecision = @lowprecision;
SELECT 
  @lowprecision AS 'lowprecision',
  @highprecision AS 'highprecision',
  TODATETIMEOFFSET(@highprecision, '+07:00') AS 'Modified';

Result (using vertical output):

lowprecision  | 2025-05-21 10:15:30.123
highprecision | 2025-05-21 10:15:30.1230000
Modified      | 2025-05-21 10:15:30.1230000 +07:00