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

When you convert a datetimeoffset value to datetime, the date and time values are copied, and the time zone offset is truncated. When the fractional precision of the datetimeoffset value is greater than three digits, the value is truncated.

The datetimeoffset data type allows you to specify a fractional seconds precision from 0 to 7. This is done 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. Another byte is used to store the precision so this adds 1 byte to those figures.

The datetime data type, on the other hand, has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds. This data type has no time zone awareness, and therefore, no time zone offset. Its storage size is 8 bytes.

Example 1 – Implicit Conversion

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

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

Result:

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

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 datetime variable.

We can see that the datetime variable has less fractional seconds precision, and we end up with a fractional part of 123 even though the original fractional part was 1234567.

In this case, no rounding was performed.

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

In this conversion, the storage size has decreased from 10 bytes (11 bytes if you include the extra byte that stores the precision) for datetimeoffset, to 8 bytes for datetime. However, if the datetimeoffset value had used a scale of between 0 and 2, it would have used only 8 bytes (9 including precision).

If it had used a scale of 3 (the equivalent of the datetime value), the storage size would have been 9 bytes (10 with precision). However, its accuracy would have been higher than datetime. Of course, the accuracy would be decreased as soon as we converted it to datetime.

Example 2 – Precision/Accuracy and Rounding

The datetime data type is rounded to increments of .000, .003, or .007 seconds. Even if you explicitly set it to another value, it will be rounded.

This also applies when converting from another data type (such as what we’re doing here).

Here’s an example that demonstrates what I mean.

DECLARE 
  @thedatetimeoffset datetimeoffset, 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.1250000 +07:00';
SET @thedatetime = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedatetime AS 'datetime';

Result:

+------------------------------------+-------------------------+
| datetimeoffset                     | datetime                |
|------------------------------------+-------------------------|
| 2025-05-21 10:15:30.1250000 +07:00 | 2025-05-21 10:15:30.127 |
+------------------------------------+-------------------------+

In this example, I set the fractional seconds of the datetimeoffset value to 1250000 but datetime rounded it up to 127 (because it can only be rounded to increments of .000, .003, or .007 seconds).

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 datetime.

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

Result:

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

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(datetime, @thedatetimeoffset) AS 'datetime';

Result:

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