This article explores the main differences between the datetime2 and smalldatetime data types in SQL Server.
Both data types are used for storing date and time values, however, there are some important differences between the two. In most cases you’re better off using datetime2 (Microsoft also recommends this), however there might be some scenarios where you need to use smalldatetime.
Here’s a table that outlines the key differences between these two types.
| Feature | smalldatetime | datetime2 |
|---|---|---|
| SQL Compliant (ANSI & ISO 8601) | No | Yes |
| Date Range | 1900-01-01 through 2079-06-06 | 0001-01-01 thru 9999-12-31 |
| Time Range | 00:00:00 through 23:59:59 | 00:00:00 thru 23:59:59.9999999 |
| Character Length | 19 positions maximum |
19 positions minimum 27 maximum |
| Storage Size | 4 bytes, fixed | 6 to 8 bytes, depending on the precision*
* Plus 1 byte to store the precision |
| Accuracy | One minute | 100 nanoseconds |
| Fractional second precision | No | Yes |
| User-defined fractional second precision | No | Yes |
| Time Zone Offset | None | None |
| Time zone offset aware and preservation | No | No |
| Daylight saving aware | No | No |
Advantages of ‘datetime2’
As seen in the above table, the datetime2 type has many advantages over smalldatetime, including:
- larger date range
- fractional seconds precision
- optional user-specified precision
- higher accuracy
- aligns with the SQL standards (ANSI & ISO 8601)
* In some cases a datetime2 value uses an extra byte to store the precision, however when stored in a database, the precision is included in the column definition, so the actual stored value doesn’t require the extra byte.
Should I use ‘datetime’ or ‘smalldatetime’?
Microsoft recommends datetime2 for new work (and for the same reasons listed above).
Therefore, you should use datetime2, unless you have a specific reason not to (such as working with a legacy system).
Example 1 – Basic Comparison
Here’s a quick example to demonstrate the basic difference between datetime2 and smalldatetime.
DECLARE @thedatetime2 datetime2(7), @thesmalldatetime smalldatetime; SET @thedatetime2 = '2025-05-21 10:15:30.5555555'; SET @thesmalldatetime = @thedatetime2; SELECT @thedatetime2 AS 'datetime2', @thesmalldatetime AS 'smalldatetime';
Result:
+-----------------------------+---------------------+ | datetime2 | smalldatetime | |-----------------------------+---------------------| | 2025-05-21 10:15:30.5555555 | 2025-05-21 10:16:00 | +-----------------------------+---------------------+
Here, I set a smalldatetime variable to the same value as the datetime2 variable. This causes the value to be converted to smalldatetime and we can then use a SELECT statement to see the value of each variable.
In this case, the datetime2 variable uses a scale of 7, which means it has 7 decimal places. The smalldatetime value on the other hand, doesn’t have any decimal places. Furthermore, its seconds are set to zero, and its minutes are rounded up.
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 datetime2 type provides a much more precise and accurate date/time value.
Of course, you might not need all those fractional seconds. One of the good things about datetime2 is that you can specify how many (if any) fractional seconds you want.
Example 2 – Using Less Decimal Places
In this example I reduce the datetime2 scale to 0:
DECLARE @thedatetime2 datetime2(0), @thesmalldatetime smalldatetime; SET @thedatetime2 = '2025-05-21 10:15:30.5555555'; SET @thesmalldatetime = @thedatetime2; SELECT @thedatetime2 AS 'datetime2', @thesmalldatetime AS 'smalldatetime';
Result:
+---------------------+---------------------+ | datetime2 | smalldatetime | |---------------------+---------------------| | 2025-05-21 10:15:31 | 2025-05-21 10:16:00 | +---------------------+---------------------+
In this case, the datetime2 value no longer includes a fractional part. Both types now share the same character length (19 positions).
But there are still differences.
The datetime2 value honors the seconds value, although in this case its seconds have been rounded up. As mentioned, the smalldatetime value’s seconds component is always set to zero, and in this case, its minutes have been rounded up.
The reason the datetime2 seconds component is rounded up is because the fractional part is 5 or higher. If we reduce the fractional part, no rounding is performed:
DECLARE @thedatetime2 datetime2(0), @thesmalldatetime smalldatetime; SET @thedatetime2 = '2025-05-21 10:15:30.4444444'; SET @thesmalldatetime = @thedatetime2; SELECT @thedatetime2 AS 'datetime2', @thesmalldatetime AS 'smalldatetime';
Result:
+---------------------+---------------------+ | datetime2 | smalldatetime | |---------------------+---------------------| | 2025-05-21 10:15:30 | 2025-05-21 10:16:00 | +---------------------+---------------------+
However, the smalldatetime value’s minutes continue to be rounded up.
Example 3 – Setting Values from String Literals
In the previous examples, the smalldateime value was assigned by setting it to the same value as the datetime2 value. When we do that, SQL Server performs an implicit conversion in order for the data to “fit” the new data type.
However, if we attempt to assign that same string literal to the smalldatetime variable, we get an error:
DECLARE @thedatetime2 datetime2(0), @thesmalldatetime smalldatetime SET @thedatetime2 = '2025-05-21 10:15:30.4444444' SET @thesmalldatetime = '2025-05-21 10:15:30.4444444' SELECT @thedatetime2 AS 'datetime2', @thesmalldatetime AS 'smalldatetime';
Result:
Msg 295, Level 16, State 3, Line 5 Conversion failed when converting character string to smalldatetime data type.
That is because smalldatetime only accepts string literals that have 3 or less fractional seconds.
You might expect that it wouldn’t accept string literals with any fractional seconds, seeing as it doesn’t include fractional seconds, but that’s not the case. It happily accepts 3 fractional seconds, but no more.
So to overcome this issue, we need to reduce the fractional part to just 3 (or less) decimal places.
DECLARE @thedatetime2 datetime2(0), @thesmalldatetime smalldatetime; SET @thedatetime2 = '2025-05-21 10:15:30.4444444'; SET @thesmalldatetime = '2025-05-21 10:15:30.444'; SELECT @thedatetime2 AS 'datetime2', @thesmalldatetime AS 'smalldatetime';
Result:
+---------------------+---------------------+ | datetime2 | smalldatetime | |---------------------+---------------------| | 2025-05-21 10:15:30 | 2025-05-21 10:16:00 | +---------------------+---------------------+
The datetime2 type doesn’t have this limitation, even when using a scale of 0.
Example 4 – Storage Size
The smalldatetime data type has a fixed storage size of 4 bytes. This is one of the few benefits smalldatetime has over datetime2.
The datetime2 can be either 6, 7, or 8 bytes, depending on its precision. So a datetime2 value will always use least 2 bytes more storage than a smalldatetime value.
Microsoft states that the datetime2 type also uses 1 extra byte in order to store its precision, in which case it would use at least 3 bytes more than smalldatetime.
However, this probably depends on whether we’re storing it in a table or in a variable, and whether or not we’re converting it to a binary constant.
Here’s what happens if we use the DATALENGTH() function to return the number of bytes used for each of our values:
DECLARE @thedatetime2 datetime2(0), @thesmalldatetime smalldatetime; SET @thedatetime2 = '2025-05-21 10:15:30'; SET @thesmalldatetime = @thedatetime2; SELECT DATALENGTH(@thedatetime2) AS 'datetime2', DATALENGTH(@thesmalldatetime) AS 'smalldatetime';
Result
+-------------+-----------------+ | datetime2 | smalldatetime | |-------------+-----------------| | 6 | 4 | +-------------+-----------------+
But if we convert them to varbinary, we get the following:
DECLARE @thedatetime2 datetime2(0), @thesmalldatetime smalldatetime; SET @thedatetime2 = '2025-05-21 10:15:30'; SET @thesmalldatetime = @thedatetime2; SELECT DATALENGTH(CAST(@thedatetime2 AS varbinary(10))) AS 'datetime2', DATALENGTH(CAST(@thesmalldatetime AS varbinary(10))) AS 'smalldatetime';
Result
+-------------+-----------------+ | datetime2 | smalldatetime | |-------------+-----------------| | 7 | 4 | +-------------+-----------------+
So datetime2 uses an extra byte when converted to varbinary. Many developers assume that converting to varbinary is representative of how SQL Server actually stores date and time values.
This is only partially true though. While it’s true that SQL Server stores its date and time values in hexadecimal, that hex value doesn’t actually include the precision. This is because the precision is included in the column definition. But when we convert to varbinary like we did in the previous example, the precision is prepended, and this adds an extra byte.
The following example demonstrates this. It shows that when the data is stored in a database column, we get a length of 6 bytes for datetime2 vs 4 bytes for smalldatetime.
Example 5 – Storage Size for Stored Data
In this example, I create a database and use COL_LENGTH to return each column’s length, in bytes. I then insert a datetime2 and smalldatetime value into it and use DBCC PAGE() to find the length of the actual data in the page file. This shows us the storage space each data type uses when stored in a database.
Create a database:
CREATE DATABASE CompareTypes;
Create a table:
USE CompareTypes;
CREATE TABLE Datetime2vsSmalldatetime (
TheDateTime2 datetime2(0),
TheSmallDateTime smalldatetime
);
In this case I create two columns – one is a datetime2(0) column and the other is a smalldatetime column.
Check the Column Length
Check the length (in bytes) of each column:
SELECT COL_LENGTH ( 'dbo.Datetime2vsSmalldatetime' , 'TheDateTime2' ) AS 'datetime2', COL_LENGTH ( 'dbo.Datetime2vsSmalldatetime' , 'TheSmallDateTime' ) AS 'smalldatetime';
Result:
+-------------+-----------------+ | datetime2 | smalldatetime | |-------------+-----------------| | 6 | 4 | +-------------+-----------------+
So we see that the datetime2(0) column has a length of 6 bytes, compared to smalldatetime‘s length of 4 bytes.
Insert Data
Now let’s look at the storage size of the actual date and time values when they’re stored in SQL Server. We can use DBCC PAGE() to inspect the actual page in the data file.
But first, we need to insert data into our columns.
Insert data:
DECLARE @thedatetime2 datetime2 = '2025-05-21 10:15:30'; INSERT INTO Datetime2vsSmalldatetime ( TheSmallDateTime, TheDateTime2 ) SELECT @thedatetime2, @thedatetime2;
Select the data (just to check it):
SELECT * FROM Datetime2vsSmalldatetime;
Result:
+---------------------+---------------------+ | TheDateTime2 | TheSmallDateTime | |---------------------+---------------------| | 2025-05-21 10:15:30 | 2025-05-21 10:16:00 | +---------------------+---------------------+
Using DBCC PAGE()
Here’s where we use DBCC PAGE() to inspect the actual page in the data file.
First, we’ll use DBCC IND() to find the PagePID:
DBCC IND('CompareTypes', 'dbo.Datetime2vsSmalldatetime', 0);
Result (using vertical output):
-[ RECORD 1 ]------------------------- PageFID | 1 PagePID | 308 IAMFID | NULL IAMPID | NULL ObjectID | 1205579333 IndexID | 0 PartitionNumber | 1 PartitionID | 72057594043039744 iam_chain_type | In-row data PageType | 10 IndexLevel | NULL NextPageFID | 0 NextPagePID | 0 PrevPageFID | 0 PrevPagePID | 0 -[ RECORD 2 ]------------------------- PageFID | 1 PagePID | 344 IAMFID | 1 IAMPID | 308 ObjectID | 1205579333 IndexID | 0 PartitionNumber | 1 PartitionID | 72057594043039744 iam_chain_type | In-row data PageType | 1 IndexLevel | 0 NextPageFID | 0 NextPagePID | 0 PrevPageFID | 0 PrevPagePID | 0
This returns two records. We’re interested in the PageType of 1 (the 2nd record). We want the PagePID from that record. In this case the PagePID is 344.
Now we can take that PagePID and use it in the following:
DBCC TRACEON(3604, -1); DBCC PAGE(CompareTypes, 1, 344, 3);
This produces a lot of data, but we’re mainly interested in the following part:
Slot 0 Column 1 Offset 0x4 Length 6 Length (physical) 6 TheDateTime2 = 2025-05-21 10:15:30 Slot 0 Column 2 Offset 0xa Length 4 Length (physical) 4 TheSmallDateTime = 2025-05-21 10:16:00.000
This shows that smalldatetime has a length of 4 bytes and datetime2(0) has 6 bytes when stored in a database.
So in this case, there’s only a 2 byte difference, but datetime2(0) is more accurate and adheres to the ANSI and ISO 8601 standards.