Search code examples
sql-serversql-server-2012

Why can integer be subtracted from DATETIME but not DATE type

Is the relationship between DATETIME & INTEGER and DATE & INTEGER consistent?

This executes fine:

DECLARE @Yesterday DATETIME = GETDATE();
SELECT @Yesterday-1;

As does this:

DECLARE @Yesterday DATE = GETDATE();
SELECT @Yesterday;

This errors:

DECLARE @Yesterday DATE = GETDATE();
SELECT @Yesterday-1;

I can safely subtract an integer type from a datetime but not from a date.
What is the reason for this behaviour?

Solution

  • Actually, it is very consistent. DATETIME is a type inherited from previous editions of SQL Server. Since 2008 edition, DATETIME2 has been introduced, and possibility of adding/subtracting integers removed. You can still do it on DATETIME as a legacy.

    DATE, like DATETIME2 had been around since 2008, too, and for this type adding/subtracting numbers is also prohibited.

    This gives you an error:

    DECLARE @Yesterday DATETIME2 = GETDATE();
SELECT @Yesterday-1;

    So everything's fine.