Search code examples
sqlsql-serverdatetimedatediffdateadd

SQL Server : DATEADD 29 February not return 30,31 January

I'm working in SQL Server 2014 and need calculate exactly one month previous from a SQL Server DateTime, but I'm unable figure out how to use DATEADD correctly.

Examples:

SELECT DATEADD(MONTH, -1, '20200229')

returns 2020-01-29.

SELECT DATEADD(MONTH, -1, '20200301')

returns 2020-02-01.

But in this second statement, I would like to get 2020-01-30 and 2020-01-31

Any ideas?

Solution

  • Seems you want end of previous month, in that case add EOMONTH() to your script which goes as follows

    Select EOMONTH( DATEADD(MONTH,-1,'20200229'));

Select EOMONTH( DATEADD(MONTH,-1,'20200301'));