CODEWITHSUNDEEP
sqlsql-serverdatediff
Alec.
Alec.

Reputation: 5525

SQL Sever - Weird DateDiff Behavior

I have the following simple query

select DATEADD(MONTH, DATEDIFF(MONTH, -32, '2020-02-29')-32, -1)
select DATEADD(MONTH, DATEDIFF(MONTH, -31, '2020-02-29')-31, -1)

I would expect the output for each line would be different due to the differing number of months in -31 and -32.

Both of these lines return 2017-07-31 00:00:00.000 and I have absolutely no idea why!

Can somebody explain this to me?

Upvotes: 0

Views: 221

Answers (2)

Thom A
Thom A

Reputation: 95574

From my comment:

Why would you expect different values? Both DATEDIFF(MONTH, -32, '20200229')-32 and DATEDIFF(MONTH, -31, '20200229')-31 result in the value 1411. The "date" -1 is 1899-12-31, and adding 1411 months (117 years 7 months) to that is 2017-07-31.

Let's break it down:

SELECT DATEDIFF(MONTH, -32, '20200229')-32 AS DD1, DATEDIFF(MONTH, -31, '20200229')-31 AS DD2;

This returns the below:

DD1         DD2
----------- -----------
1411        1411

We can break the above into further steps too. For a datetime, the date 0 is 1900-01-01 and every full integer represents adding that many days to that date. -32 as a date is therefore 1899-11-30, and -31 is 1899-12-01. This gives us the 2 below expressions:

SELECT DATEDIFF(MONTH, '18991130', '20200229') AS M1, DATEDIFF(MONTH, '18991201', '20200229') AS M2;

This returns the below:

M1          M2
----------- -----------
1443        1442

This make sense, as DATEDIFF counts the number of "ticks" between 2 dates. So for the expression DATEDIFF(YEAR, '2019-12-31T23:59:59.9999999','2020-01-01T00:00:00.000000') the value 1 is returned, even though only 1 millisecond has passed, because the value of the the year has changed (by 1).

The next part of that expression is 1443 - 32 and 1442 - 31 respectively. This is 1411 for both (basic maths).

You then have the "date" -1. You then add 1411 months (which is 117 years and 7 months) to the date 1899-12-31, which (unsurprisingly) returns the same value: 2017-07-31. 1899 + 117 = 2016. 12 + 7 = 7 because we're working in months, so carry the 1 to the year: 2017-07-31.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269653

As explained in the documentation for dateadd():

If the following are true:

  • datepart is month
  • the date month has more days than the return month
  • the date day does not exist in the return month

Then, DATEADD returns the last day of the return month.

Upvotes: 1

Related Questions