This article contains examples of converting a smalldatetime value to a datetime2 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 datetime2 data type on the other hand, allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default). If you specify zero (0), its accuracy will be to the nearest second.

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

Example 1 – Implicit Conversion

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

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

Result:

+---------------------+-----------------------------+
| smalldatetime       | datetime2                   |
|---------------------+-----------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.0000000 |
+---------------------+-----------------------------+

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

In this case, the datetime2 value uses a precision of 7. This is because 7 is the default value. I didn’t specify a precision and therefore the default value was used. This results in 7 zeros being used in the fractional part.

Example 2 – Remove the Fractional Part

You can remove the fractional seconds if required. To do this, simply use datetime2(0) when declaring the variable.

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

Result:

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

So in this case both data types return the same value. However, the difference is that the datetime2 has the ability to provide accuracy to the second (compared to smalldatetime‘s accuracy to the minute).

Example:

DECLARE 
  @thesmalldatetime smalldatetime,
  @thedatetime2 datetime2(0);
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thedatetime2 = @thesmalldatetime;
SELECT 
  DATEADD(second, 30, @thesmalldatetime) AS 'smalldatetime',
  DATEADD(second, 30, @thedatetime2) AS 'datetime2';

Result:

+---------------------+---------------------+
| smalldatetime       | datetime2           |
|---------------------+---------------------|
| 2025-05-21 10:17:00 | 2025-05-21 10:16:30 |
+---------------------+---------------------+

In this example I used the DATEADD() function to add 30 seconds to each value. However, each data type returns a different result. The datetime2 data type honors the seconds part and provides the correct result with 100% accuracy. The smalldatetime type on the other hand, is rounded up to the nearest minute (while the seconds part remains at zero).

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

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

Result:

+---------------------+---------------------+
| thesmalldatetime    | datetime2(0)        |
|---------------------+---------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00 |
+---------------------+---------------------+

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(datetime2(0), @thesmalldatetime) AS 'datetime2(0)';

Result:

+---------------------+---------------------+
| thesmalldatetime    | datetime2(0)        |
|---------------------+---------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00 |
+---------------------+---------------------+