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

One of the benefits of converting a datetime value to smalldatetime is that you reduce the storage size from 8 bytes down to 4 bytes. However, you do lose precision for doing so.

The datetime data type includes a 3 digit fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

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

When you convert a datetime 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) 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 datetime and smalldatetime.

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

Result:

+-------------------------+---------------------+
| datetime                | smalldatetime       |
|-------------------------+---------------------|
| 2025-05-21 10:15:30.123 | 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 datetime 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.

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

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

Result:

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

You might have noticed that in this example I changed the fractional seconds when assigning the initial value to @thedatetime. But because I’m assigning it to a datetime data type, the fractional seconds part is rounded up (because its accuracy is rounded to increments of .000, .003, or .007 seconds). In this case I try to assign a fractional seconds of 125 but it gets rounded up to 127.

However, this doesn’t affect the smalldatetime value.

Example 3 – Explicit Conversion using CONVERT()

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

DECLARE @thedatetime datetime;
SET @thedatetime = '2025-05-21 10:15:30.125';
SELECT 
  @thedatetime AS 'datetime',
  CONVERT(smalldatetime, @thedatetime) AS 'smalldatetime';

Result:

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