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

When you convert a smalldatetime value to datetime, the hours and minutes are copied, and 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 smalldatetime to datetime.

DECLARE @thesmalldatetime smalldatetime
SET @thesmalldatetime = '2031-03-25 11:15:29'
SELECT 
  @thesmalldatetime AS 'thesmalldatetime',
  CAST(@thesmalldatetime AS datetime) AS 'datetime';

Result:

+---------------------+-------------------------+
| thesmalldatetime    | datetime                |
|---------------------+-------------------------|
| 2031-03-25 11:15:00 | 2031-03-25 11:15:00.000 |
+---------------------+-------------------------+

Firstly, we can see that the thesmalldatetime value uses 00 for the seconds component, even though we explicitly set it at 29. This is because when using the thesmalldatetime data type, the seconds are always zero (and without fractional seconds).

Therefore, when we convert the value to datetime, the seconds value (and fractional seconds) are set to zero. Had we just assigned the original value to datetime in the first place, the seconds would’ve remained intact:

DECLARE @thedatetime datetime
SET @thedatetime = '2031-03-25 11:15:29'
SELECT @thedatetime AS 'thedatetime';

Result:

+-------------------------+
| thedatetime             |
|-------------------------|
| 2031-03-25 11:15:29.000 |
+-------------------------+

Not only would the seconds remain intact, but also any fractional seconds would’ve remained intact (but only up to a scale of 3):

DECLARE @thedatetime datetime
SET @thedatetime = '2031-03-25 11:15:29.123'
SELECT @thedatetime AS 'thedatetime';

Result:

+-------------------------+
| thedatetime             |
|-------------------------|
| 2031-03-25 11:15:29.123 |
+-------------------------+

Example 2 – Rounding

Here’s what happens if we set the seconds component to a higher value before assigning it to the smalldatetime data type.

DECLARE @thesmalldatetime smalldatetime
SET @thesmalldatetime = '2031-03-25 11:15:31'
SELECT 
  @thesmalldatetime AS 'thesmalldatetime',
  CAST(@thesmalldatetime AS datetime) AS 'datetime';

Result:

+---------------------+-------------------------+
| thesmalldatetime    | datetime                |
|---------------------+-------------------------|
| 2031-03-25 11:16:00 | 2031-03-25 11:16:00.000 |
+---------------------+-------------------------+

So the minutes component is now rounded up to the next minute.

Example 3 – Explicit Conversion using CONVERT()

This is the same as the first example, except this time I use the CONVERT() function instead of CAST().

DECLARE @thesmalldatetime smalldatetime
SET @thesmalldatetime = '2031-03-25 11:15:29'
SELECT 
  @thesmalldatetime AS 'thesmalldatetime',
  CONVERT(datetime, @thesmalldatetime) AS 'datetime';

Result:

+---------------------+-------------------------+
| thesmalldatetime    | datetime                |
|---------------------+-------------------------|
| 2031-03-25 11:15:00 | 2031-03-25 11:15:00.000 |
+---------------------+-------------------------+

Example 4 – Implicit Conversion

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

DECLARE @thesmalldatetime smalldatetime, @thedatetime datetime
SET @thesmalldatetime = '2031-03-25 11:15:29'
SET @thedatetime = @thesmalldatetime
SELECT 
  @thesmalldatetime AS 'thesmalldatetime',
  @thedatetime AS 'datetime';

Result:

+---------------------+-------------------------+
| thesmalldatetime    | datetime                |
|---------------------+-------------------------|
| 2031-03-25 11:15:00 | 2031-03-25 11:15:00.000 |
+---------------------+-------------------------+

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