0

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.

OnThaRise
  • 117
  • 1
  • 1
  • 9

1 Answers1

0

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>
eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • Hi eshirvana, that function is not available in ODBC which is why I mentioned it in my question. I can use it definitely is SSMS, but not in ODBC unfortunately. – OnThaRise May 07 '21 at 18:20
  • I have another question if possible. Is there a way to integrate the last open day of the past month or past 2 months instead of just the last day of the month by any chance? – OnThaRise May 07 '21 at 18:56
  • Hi @eshirvana, could you please elaborate on your third answer with the CASE? It doesn`t seem to be working in my WHERE clause – OnThaRise May 12 '21 at 19:16
  • @onetharise there were extra = inside case , I removed it , now it should be ok – eshirvana May 12 '21 at 22:30