DateTime2 and Arithmetic: Why It Doesn't Work Like DateTime

I came across an article on SQL Server Central written by Claudio Silva (blog / Twitter) discussing that you can’t do arithmetic operations on datetime2 like you can with datetime. It’s one of those things that bites people when migrating columns from the older type.

Why DateTime Supports Arithmetic

datetime is stored internally as two 4-byte integers: one for the date (days since January 1, 1900) and one for the time (milliseconds since midnight). Because the date portion is just an integer, SQL Server allows you to add or subtract integers directly:

1
-- this works with datetime
2
SELECT OrderDate < GETDATE() - 1

datetime2 uses a different internal storage format with variable-length precision. More importantly, Microsoft deliberately did not implement the + and - operators for datetime2 with integer operands. Attempting it throws:

1
Operand data type datetime2 is invalid for add operator.

What to Use Instead

The correct approach for both types is DATEADD and DATEDIFF, which are explicit about the date part being manipulated:

1
-- works with datetime2 (and datetime)
2
SELECT OrderDate < DATEADD(day, -1, GETDATE())

This is actually the better habit regardless of type — it makes the intent clear and avoids confusion when someone reads the code later.

Prefer DateTime2 for New Development

If you’re writing new columns or stored procedures, datetime2 is the right default:

• Higher precision (up to 100 nanoseconds vs. ~3ms for datetime)
• Wider date range (0001-01-01 through 9999-12-31)
• ANSI SQL compliant
• Smaller storage at lower precision (6–8 bytes vs. 8 bytes fixed for datetime)

The arithmetic limitation is a minor tradeoff compared to the gains. Just use DATEADD/DATEDIFF and move on.

Key Takeaways

• datetime supports integer arithmetic because its date portion is stored as an integer (days since 1900-01-01).
• datetime2 does not support +/- with integers — use DATEADD() instead.
• DATEADD(day, -1, GETDATE()) is more readable than GETDATE() - 1 in any case.
• For new development, default to datetime2 — it’s more precise, more compliant, and the arithmetic restriction is not a real limitation in practice.