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

When you convert a smalldatetime value to time, you lose the date. The hours, minutes, and seconds are copied. The fractional seconds are set to 0.

The smalldatetime data type includes both the date and time. However, the time doesn’t have any fractional seconds, and its seconds component is always set to zero (:00). Its accuracy is to the nearest minute. Its storage size is 4 bytes.

The time data type on the other hand, only includes the time. However, it 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 (the default), which provides for an accuracy of 100 nanoseconds. If you specify zero (0), its accuracy will be to the nearest second. Its storage size will be either 3, 4, or 5 bytes (plus 1 byte to store the precision), depending on the fractional seconds precision.

Example 1 – Implicit Conversion

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

DECLARE 
  @thesmalldatetime smalldatetime, 
  @thetime time;
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thetime = @thesmalldatetime;
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thetime AS 'time';

Result:

+---------------------+----------+
| smalldatetime       | time     |
|---------------------+----------|
| 2025-05-21 10:16:00 | 10:16:00 |
+---------------------+----------+

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

What’s not obvious (at least with my example) is that the time value can actually handle a fractional seconds part. My system doesn’t display the fractional seconds here, but the next example will show that it does in fact have a fractional seconds precision of 7.

Also, if you look closely, you’ll see that the smalldatetime value rounded up the minutes from the actual value I was trying to assign to it. This reflects the relatively low precision of the smalldatetime data type. Its precision is to the nearest minute. The obvious consequence of this is that when we eventually reassign its value to the time data type, it’s the rounded up value that’s assigned – not the initial value I tried to assign. If we had assigned the initial value directly to the time variable, we would have gotten a more precise value (even if we’d specified a scale of 0).

Here’s what I mean:

DECLARE 
  @thesmalldatetime smalldatetime, 
  @thetime time(0);
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thetime = '2025-05-21 10:15:30';
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thetime AS 'time';

Result:

+---------------------+----------+
| smalldatetime       | time     |
|---------------------+----------|
| 2025-05-21 10:16:00 | 10:15:30 |
+---------------------+----------+

Example 2 – Adding Fractional Seconds

The smalldatetime data type doesn’t have a fractional seconds part, but in our first example, the time value has a fractional seconds precision of 7 (even though it doesn’t actually display any decimal places). I know its precision because I didn’t specify a scale when declaring it, therefore it uses its default scale of 7.

Here’s an example to confirm that the time value can in fact, support a fractional part:

DECLARE 
  @thesmalldatetime smalldatetime, 
  @thetime time;
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thetime = @thesmalldatetime;
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thetime AS 'Original time',
  DATEADD(nanosecond, 123456700, @thetime) AS 'Modified time';

Result:

+---------------------+-----------------+------------------+
| smalldatetime       | Original time   | Modified time    |
|---------------------+-----------------+------------------|
| 2025-05-21 10:16:00 | 10:16:00        | 10:16:00.1234567 |
+---------------------+-----------------+------------------+

Note that when the time value has a scale of 7, it has a storage size of 5 bytes. Therefore, it has higher storage requirements than the smalldatetime type (which uses only 4 bytes).

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

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

Result:

+---------------------+-----------+
| smalldatetime       | time(0)   |
|---------------------+-----------|
| 2025-05-21 10:16:00 | 10:16:00  |
+---------------------+-----------+

In this example I set the scale to 0.

Example 4 – Explicit Conversion using CONVERT()

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

DECLARE @thesmalldatetime smalldatetime;
SET @thesmalldatetime = '2025-05-21 10:15:30.125';
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  CONVERT(time(0), @thesmalldatetime) AS 'time(0)';

Result:

+---------------------+-----------+
| smalldatetime       | time(0)   |
|---------------------+-----------|
| 2025-05-21 10:16:00 | 10:16:00  |
+---------------------+-----------+