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

When you convert a datetimeoffset value to datetime2, the date and time are copied to the datetime2 value, and the time zone is truncated. The fractional seconds are also truncated to fit if the destination precision is lower.

The datetimeoffset data type allows you to specify a fractional seconds precision from 0 to 7 by using the datetimeoffset(n) syntax. If you don’t specify this, it will use 7 (the default). It also has a time zone offset. This data type’s storage size is either 8, 9, or 10 bytes, depending on the precision being used.

The datetime2 data type also allows you to specify a fractional seconds precision from 0 to 7 (using the datetime2(n) syntax). It doesn’t have time zone awareness. Its storage size is either 6, 7 or 8, depending on the precision being used.

Note that the storage amounts listed here are the amounts listed in the Microsoft documentation. However, these data types also use 1 byte to store the precision. Therefore, add 1 byte to the amounts listed here for a more complete picture of the storage requirements.

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between datetimeoffset and datetime2.

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

Result:

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

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 datetimeoffset value to a datetime2 variable.

In this example, both data types use the default precision (7 decimal places). Therefore, the fractional part was 1234567 for both results.

We also see that the time zone offset was truncated – the datetime2 data type has no time zone awareness.

In this conversion, the storage size has decreased from 10 bytes (for datetimeoffset) to 8 bytes (for datetime2), although note that 1 byte is added in order to store the precision.

Example 2 – Different Precision

Here’s an example that demonstrates what happens if the datetime2 value uses a lower precision to the datetimeoffset value.

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

Result:

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

In this example, the datetimeoffset has a scale of 7 but the datetime2 value has a scale of just 3. Therefore, the fractional seconds were truncated to fit.

Note that this could cause rounding to occur. Here’s an example where the fractional part of the datetime2 value gets rounded up to 124.

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

Result:

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

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 datetimeoffset and datetime2.

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

Result:

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

Example 4 – Explicit Conversion using CONVERT()

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

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

Result:

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