Why can integer be subtracted from DATETIME but not DATE type

sql-server, sql-server-2012

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.

Problem

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?

Original source