This article explores the main differences between the datetime and smalldatetime data types in SQL Server.

Both data types are used for storing date and time values, however, there are differences between the two. In most cases you’re better off avoiding both types and using datetime2 instead (Microsoft also recommends this). In any case, here’s a comparison of these two data types.

The following table outlines some key similarities and differences between these two data types.

Feature	smalldatetime	datetime
SQL Compliant (ANSI & ISO 8601)	No	No
Date Range	1900-01-01 through 2079-06-06	1753-01-01 through 9999-12-31
Time Range	00:00:00 through 23:59:59	00:00:00 through 23:59:59.997
Character Length	19 positions maximum	19 positions minimum 23 maximum
Storage Size	4 bytes, fixed	8 bytes, fixed
Accuracy	One minute	Rounded to increments of .000, .003, or .007 seconds
Fractional second precision	No	Yes
User-defined fractional second precision	No	No
Time Zone Offset	None	None
Time zone offset aware and preservation	No	No
Daylight saving aware	No	No

Should I use ‘datetime’ or ‘smalldatetime’?

Microsoft recommends against using both of these data types for new work. You should only use them if you have a strong reason to.

But if you had to choose, your decision would likely be made by weighing up the extra precision and accuracy of datetime vs the lower storage requirements of smalldatetime.

In other words, if you don’t need accuracy to the seconds, smalldatetime will do the job while using only half the storage space. On the other hand, if you do need accuracy to the seconds (or even some fractional seconds), then you’ll need to use datetime.

In any case, Microsoft recommends using date, time, datetime2, or datetimeoffset for new work.

See smalldatetime vs datetime2 and datetime vs datetime2 to see how each of these types compare against datetime2.

Example 1 – Basic Comparison

Here’s a quick example to demonstrate the basic difference between datetime and smalldatetime.

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

Result:

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

Here, I set a smalldatetime variable to the same value as the datetime variable. This causes the value to be converted to smalldatetime and we can then use a SELECT statement to see the actual value that was assigned to each variable.

In this case, both variables round up the value. But they’re rounded up differently.

The datetime variable rounds up the fractional seconds part. This is because datetime always rounds to increments of .000, .003, or .007 seconds.

The smalldatetime variable on the other hand, rounds up the minutes part. Not only that, the seconds part is set to zero. This is to be expected, because Microsoft’s official documentation states that smalldatetime‘s time is …based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

So we can see that the datetime type provides a more precise and accurate date/time value.

Example 2 – Setting Values from String Literals

In the previous examples, the smalldateime value was assigned by setting it to the same value as the datetime value. When we do that, SQL Server performs an implicit conversion in order for the data to “fit” the new data type.

As it turns out, we can also set the smalldatetime variable to the same string literal that includes fractional seconds (even though this data type doesn’t store fractional seconds).

Here’s an example where I do just that:

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

Result:

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

Of course, the result is the same when we select the values – the smalldatetime value doesn’t show any fractional seconds, the seconds are zero, and the minutes are rounded up.

However, if we use any more than 3 decimal places, then both data types will return an error.

Error for datetime:

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

Result:

Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.

Error for smalldatetime:

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

Result:

Msg 295, Level 16, State 3, Line 5
Conversion failed when converting character string to smalldatetime data type.

Example 3 – Storage Size

The smalldatetime data type has a fixed storage size of 4 bytes. This is one of the few benefits smalldatetime has over datetime, which has a fixed storage size of 8 bytes.

We can check the storage size using the DATALENGTH() function to return the number of bytes used for each of our values:

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

Result

+------------+-----------------+
| datetime   | smalldatetime   |
|------------+-----------------|
| 8          | 4               |
+------------+-----------------+

We also get the same result even if we convert them to varbinary, which is more representative of how they’re actually stored in the database:

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

Result

+------------+-----------------+
| datetime   | smalldatetime   |
|------------+-----------------|
| 8          | 4               |
+------------+-----------------+