Search full month ORACLE

Question

I need to run a report that returns the entire amount for the previous month. He used the following form:

where cliente.dtultcomp > trunc (SYSDATE-30)

But there are cases of the month being 28, 31 days for example. There is a way to always bring me the complete previous month regardless of the amount of days in the month or day of execution?

Answer 1

In that case you could use in the WHERE to:
1. Compare current month - 1 to date month
2. Compare the current year with the year of the date

This is easy using the function EXTRACT:

The problem is in the case of month 1, which if subtracted would give 0 and the year should be subtracted as well. For this case, a simple logic using CASE WHEN:

where (case when EXTRACT(month from sysdate) = 1
           then 12
           else EXTRACT(month from sysdate)-1 end) = EXTRACT(month from cliente.dtultcomp)
  and  (case when EXTRACT(month from sysdate) = 1
           then EXTRACT(year from sysdate)-1
           else EXTRACT(year from sysdate) end) = EXTRACT(year from cliente.dtultcomp)

With that, you’ll figure it out: "return the values of the whole previous month"

An example in SQL fiddle: http://sqlfiddle.com/#! 4/db477/1

Answer 2

A simpler option would be to use the add_mouths function, which passing a negative value subtracts months from the date, ex:

...    
where cliente.dtultcomp > add_months(sysdate, -1)