Search code examples
sqlsql-serverdatediff

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?

Solution

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