Filter last month even in January-CodePudding

The filter below works for most months. However, in January I also want to pull December of the last year.

EXTRACT(MONTH FROM SYSDATE)-1 returns 0

and

EXTRACT(YEAR FROM SYSDATE)

How can I change this filter to make it more dynamic for every month of the year.

WHERE MONTH= EXTRACT(MONTH FROM SYSDATE)-1 AND YEAR = EXTRACT(YEAR FROM SYSDATE)

CodePudding user response：

Something like this, perhaps? ADD_MONTHS will subtract 1 month from sysdate (the result is DATE datatype so you have to apply to_char to it and, possibly, to_number (depending on what the month column contains)). Year, on the other hand, depends on current month - if it is January, take previous year; otherwise, take current year.

where month = to_char(add_months(sysdate, -1), 'mm')
  and year  = case when extract(month from sysdate) = 1 then extract(year from sysdate) - 1
                   else extract(year from sysdate)
              end

CodePudding user response：

Even simpler:

WHERE month = TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MM'))
  AND year =  TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY'))