If you have a datetimeoffset value, but you don’t need the date and time zone offset part, converting it to time will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a time value in SQL Server.

The datetimeoffset data type includes the date and time with a time zone offset. It also has a fractional seconds part between 0 and 7 (this depends on how many fractional seconds are assigned to it). This is done by using the datetimeoffset(n) syntax. If you don’t specify this, it will use 7 (the default). This data type’s storage size is either 8, 9, or 10 bytes, depending on the precision being used. Its accuracy is 100 nanoseconds.

The time data type on the other hand, only includes the time. It doesn’t include the date and it doesn’t include the time zone offset. However, similar to datetimeoffset it also allows you to specify a fractional seconds part between 0 and 7 (by using the time(n) syntax). It uses either 3, 4, or 5 bytes, depending on its precision.

When you convert a datetimeoffset value to a time data type, you lose the date portion. You also lose the time zone offset. However, you also reduce the storage size from between 8 and 10 bytes down to either 3, 4, or 5 bytes. However, you would only do this conversion if you don’t need the date portion or the time zone offset.

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, you’ll need to add 1 byte to the amounts listed here.

Example 1 – Implicit Conversion

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

DECLARE 
  @thedatetimeoffset datetimeoffset, 
  @thetime time;
SET @thedatetimeoffset = '2025-05-21 10:15:30.1234567 +10:30';
SET @thetime = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thetime AS 'time';

Result:

+------------------------------------+------------------+
| datetimeoffset                     | time             |
|------------------------------------+------------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 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 time variable.

Here we can see that the time value only includes the time (without the date component). The date and time zone offset components have been removed from the value.

In this example, both data types use the default precision (which results in 7 decimal places). This results in the datetimeoffset value using 10 bytes and the time value using 5 bytes.

Example 2 – Precision

The exact result will depend on the precision settings for each data type. In the next example the time value uses a lower precision to the original datetimeoffset value:

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thetime time(3);
SET @thedatetimeoffset = '2025-05-21 10:15:30.1234567 +10:30';
SET @thetime = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thetime AS 'time';

Result:

+------------------------------------+------------------+
| datetimeoffset                     | time             |
|------------------------------------+------------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 10:15:30.1230000 |
+------------------------------------+------------------+

My system displays trailing zeros, but the point is that the time value now has a precision to just 3 decimal places compared to the 7 decimal places that the original value uses.

Reducing the precision can also result in the time value being rounded up. Here’s an example:

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thetime time(3);
SET @thedatetimeoffset = '2025-05-21 10:15:30.1235555 +10:30';
SET @thetime = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thetime AS 'time';

Result:

+------------------------------------+------------------+
| datetimeoffset                     | time             |
|------------------------------------+------------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 10:15:30.1240000 |
+------------------------------------+------------------+

In this case we end up with a fractional part of 124 instead of 123, because the following digit was 5 or greater.

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

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

Result:

+------------------------------------+------------------+
| datetimeoffset                     | date             |
|------------------------------------+------------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 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 +10:30';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  CONVERT(time, @thedatetimeoffset) AS 'time'; 

Result:

+------------------------------------+------------------+
| datetimeoffset                     | date             |
|------------------------------------+------------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 10:15:30.1234567 |
+------------------------------------+------------------+