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

One of the benefits of converting a datetime2 value to smalldatetime is that you reduce the storage size from either 7, 8 or 9 bytes down to 4 bytes. However, you do lose precision.

The datetime2 data type 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.

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

When you convert a datetime2 value to smalldatetime, the date and part of the time portion is copied. The seconds component is set to zero (regardless of the original value of the seconds component) and the time is rounded to the nearest minute. Any fractional seconds are removed.

Example 1 – Implicit Conversion

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

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

Result:

+-----------------------------+---------------------+
| datetime2                   | smalldatetime       |
|-----------------------------+---------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:16: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 datetime2 value to a smalldatetime 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.

But it would’ve returned the same result regardless of the precision that I chose. Even if I’d reduced it to zero (i.e. declared it as datetime2(0)), it would still have returned the same result.

Example 2 – 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 datetime2 and smalldatetime.

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

Result:

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

In this example I decided to use a scale of zero for the datetime2 value (i.e. datetime2(0)), however, this doesn’t affect the resulting smalldatetime value.

Example 3 – Explicit Conversion using CONVERT()

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

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

Result:

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