SQL Server DATETIME vs DATETIME2 from a .NET developer's perspective

Posted: (EET/GMT+2)

 

If you're designing tables for a .NET application, you'll often find yourself choosing between SQL Server's DATETIME and DATETIME2 data types. In most cases, DATETIME2 is the better default.

Your don't need to worry (most of the time, at least) about if DATETIME2 is supported: it was introduced in SQL Server 2008 (compatibility level 100), so at this stage, it's a safe bet.

The older DATETIME type has been around since very early SQL Server versions. It works fine, but it has some limitations compared to DATETIME2.

Here's a quick comparison:

  • DATETIME range: 1753-01-01 to 9999-12-31.
  • DATETIME2 range: 0001-01-01 to 9999-12-31.
  • DATETIME precision: fixed at 3.33 ms.
  • DATETIME2 precision: 0–7 fractional seconds (100 ns increments).
  • DATETIME storage: 8 bytes.
  • DATETIME2 storage: 6–8 bytes depending on precision.

From a C# perspective, DATETIME2 maps more naturally to DateTime. The .NET DateTime type supports a range starting from year 1 and has much higher precision than SQL Server's DATETIME.

For example, this table uses DATETIME:

CREATE TABLE dbo.Events_Datetime
(
    Id int IDENTITY PRIMARY KEY,
    CreatedAt datetime NOT NULL
);

And the same table using DATETIME2:

CREATE TABLE dbo.Events_Datetime2
(
    Id int IDENTITY PRIMARY KEY,
    CreatedAt datetime2(7) NOT NULL
);

When inserting values from C#, DATETIME may round the timestamp:

INSERT INTO dbo.Events_Datetime (CreatedAt)
VALUES ('2025-11-15T10:15:30.1234567');

The stored value will be rounded to the nearest supported increment. With DATETIME2, the full precision is preserved.

In SQL Server Management Studio, both types behave the same in most day-to-day work. You can select, filter, and sort them identically. The main visible difference is the precision shown in query results or table designers.

For new tables, DATETIME2 is generally the recommended choice. It offers a wider range, better precision, and more efficient storage at lower precisions. The only real reason to keep DATETIME is compatibility with older schemas or legacy applications.