The interval of one month ago does not work on the 31st?

Question

Essentially, I have a query that is responsible for fetching all records (with specific filters) for the last month. I use the Oracle interval keyword and everything works fine until today (December 31, 2009). The code I'm using is

select (sysdate - interval '1' month) from dual 

and the error i get

 ORA-01839: date not valid for month specified 

How to use the interval keyword for compatibility with any date? Or, if someone has a better way to approach the problem, I’m all ears.

Thanks.

Answer 1

try

 select add_months(sysdate,-1) from dual 

Answer 2

Being pedantic ...

The requirements are not well defined. What does a "last month" business mean? Most people think this means “during the current calendar month,” in which case I would use:

 TRUNC(SYSDATE,'MM') 

Otherwise, perhaps they need an arbitrary period of 1 month before the current date - but how do you determine this? As you have found, INTERVAL '1' MONTH simply subtracts one of the month part of the date - for example. 15-JAN-2009 - INTERVAL '1' MONTH returns 15-DEC-1999. For some dates, this leads to an invalid date because not all months have the same number of days.

ADD_MONTHS resolves this by returning the last day of the month, for example. ADD_MONTHS(31-DEC-2009,-1) returns 30-NOV-2009.

Another possibility is that the business actually wants to use the average monthly period - for example, 365/12, which is about 30.4. They may want to use SYSDATE-30 , although, of course, twelve iterations of this will cover only 360 days a year.