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

When you convert a date value to smalldatetime, extra information is added to the value. This is because the smalldatetime data type contains both date and time information. The date data type, on the other hand, only contains date information.

However, there are cases where a date to smalldatetime conversion might fail. In particular, if the date value is outside the range supported by smalldatetime then it will fail with an error.

In any case, below are examples of converting between these two data types.

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between date and smalldatetime.

DECLARE @thedate date, @thesmalldatetime smalldatetime
SET @thedate = '2020-12-01'
SET @thesmalldatetime = @thedate
SELECT 
  @thedate AS 'date',
  @thesmalldatetime AS 'smalldatetime';

Result:

+------------+---------------------+
| date       | smalldatetime       |
|------------+---------------------|
| 2020-12-01 | 2020-12-01 00:00: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 date value to a smalldatetime variable.

We can see that the date variable only contains date information, whereas, the smalldatetime variable contains both date and time information.

When you convert between date and smalldatetime, the time component is set to 00:00:00. This provides for an accuracy to the minute.

The reason it’s all zeros is because the date value doesn’t contain any time information, so there’s no way for SQL Server to know what time you want (if any).

Of course, you also get this result even if you simply assign a date-only value to a smalldatetime without performing a conversion:

DECLARE @thesmalldatetime smalldatetime = '2020-12-01'
SELECT @thesmalldatetime AS 'smalldatetime';

Result:

+---------------------+
| smalldatetime       |
|---------------------|
| 2020-12-01 00:00:00 |
+---------------------+

Example 2 – Modify the Time

If you need to specify a time (but keep the same date), you can use the DATEADD() function to do just that.

DECLARE @thedate date, @thesmalldatetime smalldatetime
SET @thedate = '2020-12-01'
SET @thesmalldatetime = @thedate
SET @thesmalldatetime = DATEADD(hour, 7, @thesmalldatetime)
SELECT 
  @thedate AS 'date',
  @thesmalldatetime AS 'smalldatetime';

Result:

+------------+---------------------+
| date       | smalldatetime       |
|------------+---------------------|
| 2020-12-01 | 2020-12-01 07:00:00 |
+------------+---------------------+

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

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  CAST(@thedate AS smalldatetime) AS 'smalldatetime';

Result:

+------------+---------------------+
| date       | smalldatetime       |
|------------+---------------------|
| 2020-12-01 | 2020-12-01 00:00:00 |
+------------+---------------------+

Same result as the implicit conversion.

We can also adjust the time like this:

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  DATEADD(hour, 7, CAST(@thedate AS smalldatetime)) AS 'smalldatetime';

Result:

+------------+---------------------+
| date       | smalldatetime       |
|------------+---------------------|
| 2020-12-01 | 2020-12-01 07:00:00 |
+------------+---------------------+

Example 4 – Explicit Conversion using CONVERT()

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

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  CONVERT(smalldatetime, @thedate) AS 'smalldatetime';

Result:

+------------+---------------------+
| date       | smalldatetime       |
|------------+---------------------|
| 2020-12-01 | 2020-12-01 00:00:00 |
+------------+---------------------+

And adjusting the time:

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  DATEADD(hour, 7, CONVERT(smalldatetime, @thedate)) AS 'smalldatetime';

Result:

+------------+---------------------+
| date       | smalldatetime       |
|------------+---------------------|
| 2020-12-01 | 2020-12-01 07:00:00 |
+------------+---------------------+

Example 5 – Out of Range Error

As mentioned, if the date is outside the range supported by the smalldatetime data type, you’ll get an error.

DECLARE @thedate date
SET @thedate = '2080-12-01'
SELECT 
  @thedate AS 'date',
  CAST(@thedate AS smalldatetime) AS 'smalldatetime';

Result:

The conversion of a date data type to a smalldatetime data type resulted in an out-of-range value.

The smalldatetime data type only supports date ranges 1900-01-01 through 2079-06-06.

Also, the smalldatetime data type only supports time ranges 00:00:00 through 23:59:59, so you’ll also get an error if you try to use a value outside that range (for example, with a higher precision).

Example:

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  DATEADD(nanosecond, 7, CAST(@thedate AS smalldatetime)) AS 'smalldatetime';

Result:

The datepart nanosecond is not supported by date function dateadd for data type smalldatetime.

However, in this case, it’s not an error while converting, it’s actually an error while using the DATEADD() function (because the function doesn’t allow that particular datepart to be used on a smalldatetime data type).