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

When you convert a time value to smalldatetime, the date is set to ‘1900-01-01’, and the hour and minute values are rounded up. The seconds and fractional seconds are set to 0.

Example 1 – 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 from time to smalldatetime.

DECLARE @thetime time;
SET @thetime = '23:15:59.1234567';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS smalldatetime) AS 'smalldatetime';

Result:

+------------------+---------------------+
| time             | smalldatetime       |
|------------------+---------------------|
| 23:15:59.1234567 | 1900-01-01 23:16:00 |
+------------------+---------------------+

So a date part is added and set to ‘1900-01-01’, and in this case the minute values are rounded up and the seconds set to 0.

The Microsoft documentation states that the fractional seconds are also set to zero, but the smalldatetime data type doesn’t include fractional seconds anyway.

By the way, whenever you use the smalldatetime data type, the seconds component is always set to 0.

Example 2 – Rounding up the Hour

Here’s an example of the hour being rounded up:

DECLARE @thetime time(0);
SET @thetime = '10:59:59';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS smalldatetime) AS 'smalldatetime';

Result:

+----------+---------------------+
| time     | smalldatetime       |
|----------+---------------------|
| 10:59:59 | 1900-01-01 11:00:00 |
+----------+---------------------+

In this case I also specified a scale of 0 for the time value, however, this doesn’t affect the outcome.

Just to be clear, scale is the number of digits to the right of the decimal point in a number. Precision is the total number of digits in the number. When we specify a scale of 0, it means the fractional part is not included.

Example 3 – Explicit Conversion using CONVERT()

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

DECLARE @thetime time;
SET @thetime = '23:15:59.1234567';
SELECT 
  @thetime AS 'time',
  CONVERT(smalldatetime, @thetime) AS 'smalldatetime';

Result:

+------------------+---------------------+
| time             | smalldatetime       |
|------------------+---------------------|
| 23:15:59.1234567 | 1900-01-01 23:16:00 |
+------------------+---------------------+

Example 4 – Implicit Conversion

Here’s an example of doing the same thing, but using an implicit type conversion.

DECLARE @thetime time, @thesmalldatetime smalldatetime;
SET @thetime = '23:15:59.1234567';
SET @thesmalldatetime = @thetime;
SELECT 
  @thetime AS 'time',
  @thesmalldatetime AS 'smalldatetime';

Result:

+------------------+---------------------+
| time             | smalldatetime       |
|------------------+---------------------|
| 23:15:59.1234567 | 1900-01-01 23:16:00 |
+------------------+---------------------+

So we get the same result, regardless of whether it’s an explicit or implicit conversion.

This is an implicit conversion because we’re not using a conversion function to explicitly convert it. We’re simply assigning the value from a variable of one data type to a variable of another data type. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the time value to a smalldatetime variable.

Example 5 – Change the Date

If you need to change the date (but keep the same time), you can use the DATEADD() function.

DECLARE @thetime time, @thesmalldatetime smalldatetime;
SET @thetime = '23:15:59.1234567';
SET @thesmalldatetime = @thetime;
SET @thesmalldatetime = DATEADD(year, 85, @thesmalldatetime);
SELECT 
  @thetime AS 'time',
  @thesmalldatetime AS 'smalldatetime';

Result:

+------------------+---------------------+
| time             | smalldatetime       |
|------------------+---------------------|
| 23:15:59.1234567 | 1985-01-01 23:16:00 |
+------------------+---------------------+

In this case I add 85 to the year value, which brings it to 1985.

Be aware though, that smalldatetime supports a very narrow date range (1900-01-01 through 2079-06-06), so adding too much to the year could result in an overflow error like the one below:

DECLARE @thetime time, @thesmalldatetime smalldatetime;
SET @thetime = '23:15:59.1234567';
SET @thesmalldatetime = @thetime;
SET @thesmalldatetime = DATEADD(year, 220, @thesmalldatetime);
SELECT 
  @thetime AS 'time',
  @thesmalldatetime AS 'smalldatetime';

Result:

Adding a value to a 'smalldatetime' column caused an overflow.