Getting Last Day of Month (2 Months Ago) in ODBC

Question

I'm trying to get the last day of 2 months ago on an ODBC server but can't seem to get it quite right as the EOMONTH built-in function of SSMS is not functional in ODBC.

The part of the code that I currently have in my WHERE clause returns the 1st day of the last month. I'm trying to get the day before that, which would be as described, the last day of 2 months ago.

Here's the code that I'm currently using:

WHERE x_date = cast(dateadd(month, -1, dateadd(day, 1 - day(getdate()), getdate())) as date)

Currently, we are May 7th, 2021. That WHERE clause returns me April 1st, 2021. I would like it to return me March 31st, 2021.

I've tried other WHERE clauses, without any positive results.

Any help would be greatly appreciated.

Thank you.

Answer 1

you can use EOMONTH function in sql server :

SELECT EOMONTH(GETDATE(), -2)

if it is not supported then this should work:

SELECT DATEADD(dd,-(DAY(DATEADD(mm,-1,getdate()))),DATEADD(mm,-1,getdate()))

by open day if you mean weekdays:

select case  DATENAME(weekday , <above date>)
        when  'Saturday' then dateadd(day , 2 , <above date>)
        when  'sunday' then dateadd(day , 1 , <above date>)
        else  <above date>