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)`

Accepted Answer

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`...

Confusion over the second argument of the DATEDIFF function

Solution

Problem