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

The smalldatetime data type doesn’t have any fractional seconds, and its seconds component is always set to zero (:00). Its accuracy is to the nearest minute.

The datetime data type on the other hand, includes a 3-digit fractional seconds part, and its accuracy is rounded to increments of .000, .003, or .007 seconds.

When you convert a smalldatetime value to datetime, the smalldatetime value is copied to the datetime value. The fractional seconds are set to 0.

In most cases, you’d be better off converting to a datetime2 data type instead of a datetime. Doing this will provide increased accuracy, while using the same storage size. However, if you really do need it to be datetime, here are some examples.

Example 1 – Implicit Conversion

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

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

Result:

+---------------------+-------------------------+
| smalldatetime       | datetime                |
|---------------------+-------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.000 |
+---------------------+-------------------------+

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 datetime variable.

In this example we can see that the smalldatetime value doesn’t include the fractional seconds, the seconds have been set to zero, and the minute has been rounded up from the actual value that I tried to assign to it.

However, the datetime value includes a 3-digit fractional part that is set to zero.

Example 2 – Change the Fractional Part

Even though the fractional part is set to zero, once you’ve converted the value to datetime, you are now able to change the fractional part (and the seconds part for that matter).

In this example, I use the DATEADD() function to modify the fractional seconds of datetime value.

DECLARE 
  @thesmalldatetime smalldatetime,
  @thedatetime datetime;
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thedatetime = @thesmalldatetime;
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thedatetime AS 'datetime',
  DATEADD(millisecond, 123, @thedatetime) AS 'datetime Modified';

Result (using vertical output):

smalldatetime     | 2025-05-21 10:16:00
datetime          | 2025-05-21 10:16:00.000
datetime Modified | 2025-05-21 10:16:00.123

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

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

Result:

+---------------------+-------------------------+
| thesmalldatetime    | datetime                |
|---------------------+-------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.000 |
+---------------------+-------------------------+

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';
SELECT 
  @thesmalldatetime AS 'thesmalldatetime',
  CONVERT(datetime, @thesmalldatetime) AS 'datetime';

Result:

+---------------------+-------------------------+
| thesmalldatetime    | datetime                |
|---------------------+-------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.000 |
+---------------------+-------------------------+

As mentioned, consider converting to a datetime2 data type instead of datetime. Doing this provides increased accuracy, while using the same storage size.