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

One of the benefits of converting a datetime value to time is that you reduce the storage size from 8 bytes, down to either 3, 4, or 5 bytes (depending on the precision you use for the time value). Strictly speaking, time uses 4, 5, or 6 bytes, because an extra byte is used to store its precision.

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

The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

The time data type, on the other hand, allows you to specify a fractional seconds precision from 0 to 7. This is achieved by using the time(n) syntax, where n is the scale from 0 to 7. If you don’t specify this, it will use 7, which provides for an accuracy of 100 nanoseconds.

Example 1 – Implicit Conversion

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

DECLARE 
  @thedatetime datetime, 
  @thetime time;
SET @thedatetime = '2025-05-21 10:15:30.123';
SET @thetime = @thedatetime;
SELECT 
  @thedatetime AS 'datetime',
  @thetime AS 'time';

Result:

+-------------------------+------------------+
| datetime                | time             |
|-------------------------+------------------|
| 2025-05-21 10:15:30.123 | 10:15:30.1233333 |
+-------------------------+------------------+

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 datetime value to a time variable.

The most obvious 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.

We can also see that the time variable has more fractional seconds precision, and we end up with a fractional part of 1233333 (vs 123 for the datetime value). This happens because the time value is using the default scale of 7 (because we didn’t explicitly specify a scale).

Example 2 – 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.

Here’s an example:

DECLARE 
  @thedatetime datetime, 
  @thetime time;
SET @thedatetime = '2025-05-21 10:15:30.125';
SET @thetime = @thedatetime;
SELECT 
  @thedatetime AS 'datetime',
  @thetime AS 'thetime';

Result:

+-------------------------+------------------+
| datetime                | thetime          |
|-------------------------+------------------|
| 2025-05-21 10:15:30.127 | 10:15:30.1266667 |
+-------------------------+------------------+

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

The time value however, set the fractional seconds to 1266667.

If we’d simply set the initial value to time in the first place, its fractional part would’ve returned 1250000.

Example 3 – Precision/Accuracy

As mentioned, the time data type allows you to specify the fractional seconds precision. If you don’t do this, it uses 7 (therefore, the previous example uses 7).

You can modify this by using the time(n) syntax. Therefore, I could have used time(7) for the previous examples to get the same result.

In this example, I remove the fractional seconds altogether by using time(0):

DECLARE 
  @thedatetime datetime, 
  @thetime time(0);
SET @thedatetime = '2025-05-21 10:15:30.125';
SET @thetime = @thedatetime;
SELECT 
  @thedatetime AS 'datetime',
  @thetime AS 'time';

Result:

+-------------------------+----------+
| datetime                | time     |
|-------------------------+----------|
| 2025-05-21 10:15:30.127 | 10:15:30 |
+-------------------------+----------+

When we do this, the storage size of the time value is reduced to 3 bytes (4 bytes including precision) as opposed to 8 bytes for the datetime value.

Be aware that using a lower precision than the original value will cause the result to be rounded up to fit the specified precision.

Example:

DECLARE 
  @thedatetime datetime, 
  @thetime time(0);
SET @thedatetime = '2025-05-21 10:15:30.525';
SET @thetime = @thedatetime;
SELECT 
  @thedatetime AS 'datetime',
  @thetime AS 'time';

Result:

+-------------------------+----------+
| datetime                | time     |
|-------------------------+----------|
| 2025-05-21 10:15:30.527 | 10:15:31 |
+-------------------------+----------+

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

DECLARE @thedatetime datetime;
SET @thedatetime = '2025-05-21 10:15:30.125';
SELECT 
  @thedatetime AS 'datetime',
  CAST(@thedatetime AS time(0)) AS 'time(0)';

Result:

+-------------------------+-----------+
| datetime                | time(0)   |
|-------------------------+-----------|
| 2025-05-21 10:15:30.127 | 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 @thedatetime datetime;
SET @thedatetime = '2025-05-21 10:15:30.125';
SELECT 
  @thedatetime AS 'datetime',
  CONVERT(time(0), @thedatetime) AS 'time(0)';

Result:

+-------------------------+-----------+
| datetime                | time(0)   |
|-------------------------+-----------|
| 2025-05-21 10:15:30.127 | 10:15:30  |
+-------------------------+-----------+