Get dates of previous 3 months from the current month in Oracle Sql

Question

I got a Oracle Sql code where I want the dates.

select abc,eff_dt 
from xyz 
where eff_dt between 'a' and 'b'

I am looking to get dates from previous 3 months.

1. For example if current date is 5/26/2023 then I want a=02/01/2023 and b=04/30/2023.
2. For example if current date is 6/01/2023 then I want a=03/01/2023 and b=05/31/2023.

Answer 1

You can truncate the current date to the first day of the month, then use interval arithmetic. Probably you want to use half-open intervals rather than computing the last day of the month (this is usually safer, especially if your dates have a time portion):

where eff_dt >= trunc(sysdate, 'mon') - interval '3' month
  and eff_dt <  trunc(sysdate, 'mon')

Of course this assumes that eff_dt of a date-like datatype.