sqlcasesysdate

SQL to return dates between yesterday and the last two/one full months based on today's date

I am trying to pull in data based on a date field with the following logic: where (case when to_char(sysdate,'d') < 6 then a.dte_entered between last_day(ADD_MONTHS((SYSDATE-1),-3)) and TRUNC(SYSDATE - 1) else a.dte_entered between last_day(ADD_MONTHS(SYSDATE,-2)) and TRUNC(SYSDATE - 1) end)

Example: if today's date is October 5, then I need all data that falls between August 1 and October 4 if today's date is October 10, then I need dates between September 1 and October 9

Solution

  • How about this?

    where a.dte_entered >= (trunc(sysdate, 'MON') - 
                        (case when extract(day from sysdate) < 6 then interval '2' month else interval '1' month
                        )
                       ) and
      a.dte_entered < trunc(sysdate)