Confusion over the second argument of the DATEDIFF function

Question

I've referred to this on MSDN but I'm still unsure what the second argument in the DATEDIFF function is doing in the following two examples:

SELECT DATEDIFF(yy,0,getdate()) --run on 14 Aug this returns 112

SELECT DATEDIFF(yy,1000,getdate()) --I chose 1000 arbitrarily and run on 14 Aug this returns 110

Usually I'll use DATEDIFF to find the number of days, or number of years between two months and the second argument is then a date.

Reason I'd like to understand the above is to ultimately understand the following:

SELECT DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0)

Answer 1

If you use an integer as the second argument (or for any datetime/smalldatetime assignment for that matter), this is interpreted as the number of days since 1900-01-01.

DECLARE @d1 DATETIME = 0, @d2 DATETIME = 1;
SELECT @d1, @d2;

Result:

1900-01-01 00:00:00.000    1900-01-02 00:00:00.000

Note that this doesn't work for new data types like DATE during direct assignment:

DECLARE @d DATE = 0;

Result:

Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with date

But it can still work using date math, e.g.:

DECLARE @d DATE = DATEADD(YEAR, 0, SYSDATETIME());
SELECT @d;

Result:

2012-08-14

For these inconsistent reasons, I recommend you use proper date literals so that it is clear which date you mean and so that it works regardless of the data type. This is a habit I find hard to break, since typing 0 is so much easier than 19000101...

Answer 2

Consider below example to understand this concept better. 0 is default date "1900-01-01"

Below Query gives output as 2017-10-31 00:00:00.000

SELECT    DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH,0,SYSDATETIME())+3, 0)) 

Dividing the above query into various parts to understand the calculation.

-- 1414 Months since '1900-01-01'
    SELECT DATEDIFF(MONTH,0,SYSDATETIME())  + 3 

-- adding 1414 Months to Default date 1900 Produces '2017-11-01 00:00:00.000'
    SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,SYSDATETIME())+3, 0) 

--Subtract 1 day from '2017-11-01 00:00:00:000' gives last day of previous month.
    SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH,0,SYSDATETIME())+3, 0))