datetime vs datetime2 in SQL Server: What’s the Difference?

Posted on by Ian

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

If you’re not sure which one to use, use datetime2 (see its advantages below).

Here’s a table that outlines the key differences between these two types.

Feature datetime datetime2
SQL Compliant (ANSI & ISO 8601) No Yes
Date Range 1753-01-01 thru 9999-12-31 0001-01-01 thru 9999-12-31
Time Range 00:00:00 thru 23:59:59.997 00:00:00 thru 23:59:59.9999999
Character Length 19 positions minimum
23 maximum 		19 positions minimum
27 maximum
Storage Size 8 bytes 6 to 8 bytes, depending on the precision*

* Plus 1 byte to store the precision
Accuracy Rounded to increments of .000, .003, or .007 seconds 100 nanoseconds
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 datetime, including:

  • larger date range
  • larger default fractional precision
  • optional user-specified precision
  • higher accuracy, even when using using the same number of decimal places as datetime (i.e. 3)
  • less storage size when using using the same number of decimal places as datetime, yet with higher accuracy*
  • the option to use 2 bytes less storage than datetime (albeit with lower precision)*
  • aligns with the SQL standards (ANSI & ISO 8601)

* In some cases a datetime2 value uses an extra byte to store the precision, which would result in the same storage size as datetime when using the same number of decimal places. Read on to find out more about this.

Should I use ‘datetime’ or ‘datetime2’?

Microsoft recommends datetime2 over datetime 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 datetime and datetime2.

DECLARE 
  @thedatetime2 datetime2(7), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5555555';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-----------------------------+-------------------------+
| datetime2                   | datetime                |
|-----------------------------+-------------------------|
| 2025-05-21 10:15:30.5555555 | 2025-05-21 10:15:30.557 |
+-----------------------------+-------------------------+

Here, I set a datetime variable to the same value as the datetime2 variable. This causes the value to be converted to datetime and we can then use a SELECT statement to see the result.

In this case, the datetime2 variable uses a scale of 7, which means 7 decimal places. The datetime value on the other hand, uses just 3 decimal places, and its last fractional digit is rounded up (because this data type rounds the fractional seconds to increments of .000, .003, or .007 seconds).

Example 2 – Using 3 Decimal Places

If I reduce the datetime2 scale to 3 (to match dateime), here’s what happens.

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5555555';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-------------------------+-------------------------+
| datetime2               | datetime                |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.556 | 2025-05-21 10:15:30.557 |
+-------------------------+-------------------------+

So the datetime2 value is also rounded up in this case. However, it’s only rounded up to 556 – it doesn’t jump to 557 like the datetime value does.

Of course, the only reason the datetime2 value is rounded up is because the following digit is 5 or higher. If we reduce the following digit, no rounding is performed:

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5554444';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-------------------------+-------------------------+
| datetime2               | datetime                |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.555 | 2025-05-21 10:15:30.557 |
+-------------------------+-------------------------+

However, the datetime value continues to be rounded up.

Example 3 – Setting Values from String Literals

In the previous examples, the dateime 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 the same string literal to the datetime variable that we assigned to datetime2, we get an error:

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5554444';
SET @thedatetime = '2025-05-21 10:15:30.5554444';
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

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

This is because datetime only accepts string literals that have 3 or less fractional seconds.

So to overcome this issue, we need to reduce the fractional part to just 3 (or less) decimal places.

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5554444';
SET @thedatetime = '2025-05-21 10:15:30.555';
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-------------------------+-------------------------+
| datetime2               | datetime                |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.555 | 2025-05-21 10:15:30.557 |
+-------------------------+-------------------------+

The datetime2 type doesn’t have this limitation, even when using a scale of 3.

Example 4 – Storage Size

The datetime data type has a fixed storage size of 8 bytes.

The datetime2 on the other hand, can be either 6, 7, or 8 bytes, depending on its precision.

When using 3 decimal places, datetime2 uses just 7 bytes, which means it uses less storage space than datetime (with more accuracy).

However, Microsoft states that the datetime2 type also uses 1 extra byte in order to store its precision. So in this case, it would use 8 bytes. And we can therefore revise the previous statement by saying that it uses either 7, 8, or 9 bytes.

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(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5554444';
SET @thedatetime = @thedatetime2;
SELECT 
  DATALENGTH(@thedatetime2) AS 'datetime2',
  DATALENGTH(@thedatetime) AS 'datetime';

Result

+-------------+------------+
| datetime2   | datetime   |
|-------------+------------|
| 7           | 8          |
+-------------+------------+

But if we convert them to varbinary, we get the following:

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.5554444';
SET @thedatetime = @thedatetime2;
SELECT 
  DATALENGTH(CONVERT(VARBINARY(16),@thedatetime2)) AS 'datetime2',
  DATALENGTH(CONVERT(VARBINARY(16),@thedatetime)) AS 'datetime';

Result

+-------------+------------+
| datetime2   | datetime   |
|-------------+------------|
| 8           | 8          |
+-------------+------------+

So datetime2 uses an extra byte when converted to varbinary, thus bringing it to the same storage size as datetime.

However, the following example shows that when the data is stored in a database column, we get a length of 7 bytes for datetime2 and 8 bytes for datetime.

When storing datetime2 values in a database, the column definition includes the precision. In this case the values in each row don’t need the extra byte to store the precision, and we can say that datetime2 uses less storage space than datetime when using the same number of fractional seconds.

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 datetime 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 Datetime2vsDatetime (
    TheDateTime datetime,
    TheDateTime2 datetime2(3)
    );

In this case I create two columns – one is a datetime column and the other is a datetime2 column.

Check the Column Length

Check the length (in bytes) of each column:

SELECT 
  COL_LENGTH ( 'dbo.Datetime2vsDatetime' , 'TheDateTime2' ) AS 'datetime2',
  COL_LENGTH ( 'dbo.Datetime2vsDatetime' , 'TheDateTime' ) AS 'datetime';

Result:

+-------------+------------+
| datetime2   | datetime   |
|-------------+------------|
| 7           | 8          |
+-------------+------------+

So we see that the datetime2 column has a length of 7 bytes, compared to datetime‘s length of 8 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.5554444';
INSERT INTO Datetime2vsDatetime ( TheDateTime, TheDateTime2 )
SELECT @thedatetime2, @thedatetime2;

Select the data (just to check it):

SELECT * FROM Datetime2vsDatetime;

Result:

+-------------------------+-------------------------+
| TheDateTime             | TheDateTime2            |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.557 | 2025-05-21 10:15:30.555 |
+-------------------------+-------------------------+

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.Datetime2vsDatetime', 0);

Result (using vertical output):

-[ RECORD 1 ]-------------------------
PageFID         | 1
PagePID         | 307
IAMFID          | NULL
IAMPID          | NULL
ObjectID        | 885578193
IndexID         | 0
PartitionNumber | 1
PartitionID     | 72057594042974208
iam_chain_type  | In-row data
PageType        | 10
IndexLevel      | NULL
NextPageFID     | 0
NextPagePID     | 0
PrevPageFID     | 0
PrevPagePID     | 0
-[ RECORD 2 ]-------------------------
PageFID         | 1
PagePID         | 320
IAMFID          | 1
IAMPID          | 307
ObjectID        | 885578193
IndexID         | 0
PartitionNumber | 1
PartitionID     | 72057594042974208
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 320.

Now we can take that PagePID and use it in the following:

DBCC TRACEON(3604, -1);
DBCC PAGE(CompareTypes, 1, 320, 3);

This produces a lot of data, but we’re mainly interested in the following part:

Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

TheDateTime = 2025-05-21 10:15:30.557                                    

Slot 0 Column 2 Offset 0xc Length 7 Length (physical) 7

TheDateTime2 = 2025-05-21 10:15:30.555

This shows that datetime uses a length of 8 bytes and datetime2(3) uses 7 bytes when stored in a database.

So this reinforces the case for using datetime2 over datetime when designing new databases, especially if storage size is a concern.