CODEWITHSUNDEEP
sqloracle-databaseplsql
user16400720
user16400720

Reputation:

Filter last month even in January

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)

Upvotes: 0

Views: 68

Answers (3)

MT0
MT0

Reputation: 168740

Subtract a month before using EXTRACT:

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

Upvotes: 1

Paul W
Paul W

Reputation: 11791

Even simpler:

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143103

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

Upvotes: 0

Related Questions