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

One of the benefits of converting a datetime2 value to time is that you reduce the storage size from between 6 and 8 bytes, down to between 3 and 5 bytes (depending on the precision each data type has assigned to it). Strictly speaking, 1 extra byte is used to store the precision for these data types, so you should add 1 byte to these amounts.

Obviously, you do lose the date portion during the conversion, but you wouldn’t be doing this conversion if you needed to retain the date portion.

When you convert a datetime2 value to time, only the time portion of the value is copied. The exact result will depend on the fractional seconds precision that are assigned to each type. When the time precision is less than the datetime2 precision, the fractional seconds is rounded up to fit the time precision.

Regarding precision, both data types allow you to specify a scale from 0 decimal places up to 7. Therefore, you have the ability to perform the conversion without losing any fractional seconds or having the result rounded up.

Example 1 – Implicit Conversion

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

DECLARE 
  @thedatetime2 datetime2, 
  @thetime time;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SET @thetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thetime AS 'time';

Result:

+-----------------------------+------------------+
| datetime2                   | time             |
|-----------------------------+------------------|
| 2025-05-21 10:15:30.1234567 | 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 datetime2 value to a time variable.

The most apparent thing about this result is that the time value doesn’t include the date. This is to be expected, because the time data type is solely for storing time values, not date values.

Also, in this example, both data types use their default precision (which is a scale of 7). This means that the time value ends up with the same precision as the datetime2 value. The reason I know they use their default precision is because I didn’t specify a fractional second scale when declaring them.

Example 2 – Convert to a Higher Precision

In this example the time variable uses higher precision to the datetime2 variable.

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

Result:

+--------------------------+------------------+
| datetime2                | time             |
|--------------------------+------------------|
| 2025-05-21 10:15:30.1235 | 10:15:30.1235000 |
+--------------------------+------------------+

In this case the datetime2 variable uses a scale of 4, and the time variable uses 7.

This results in datetime2 rounding up the fractional seconds, so we get 1235 instead of 1234. It also means that when we convert it to time, the value ends up with 3 trailing zeros (because we specified a scale of 7). This also means that the time value’s storage size is 5 bytes (6 including precision) compared to 4 bytes (5 including precision) if we’d given it a scale of 4.

Example 3 – Convert to a Lower Precision

In this example the time variable uses lower precision to the datetime2 variable.

DECLARE 
  @thedatetime2 datetime2(7), 
  @thetime time(0);
SET @thedatetime2 = '2025-05-21 10:15:30.5678912';
SET @thetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thetime AS 'time';

Result:

+-----------------------------+----------+
| datetime2                   | time     |
|-----------------------------+----------|
| 2025-05-21 10:15:30.5678912 | 10:15:31 |
+-----------------------------+----------+

So in this case time‘s scale is reduced to 0, which means no fractional seconds are included in the result. Also, the seconds are rounded up accordingly.

The time value uses 4 bytes storage (including precision), compared to 9 bytes for the datetime2 value (and compared to 6 bytes for the time value in the previous example).

Example 4 – 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 time.

DECLARE @thedatetime2 datetime2(3);
SET @thedatetime2 = '2025-05-21 10:15:30.123';
SELECT 
  @thedatetime2 AS 'datetime2',
  CAST(@thedatetime2 AS time(0)) AS 'time(0)';

Result:

+-------------------------+-----------+
| datetime2               | time(0)   |
|-------------------------+-----------|
| 2025-05-21 10:15:30.123 | 10:15:30  |
+-------------------------+-----------+

Example 5 – Explicit Conversion using CONVERT()

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

DECLARE @thedatetime2 datetime2(3);
SET @thedatetime2 = '2025-05-21 10:15:30.123';
SELECT 
  @thedatetime2 AS 'datetime2',
  CONVERT(time(0), @thedatetime2) AS 'time(0)';

Result:

+-------------------------+-----------+
| datetime2               | time(0)   |
|-------------------------+-----------|
| 2025-05-21 10:15:30.123 | 10:15:30  |
+-------------------------+-----------+