Interval of one month back not working on the 31st?

Question

Essentially, I have a query that is responsible for fetching all records (with specific filters) within the last month. I'm using Oracle's interval keyword and all was working great until today (December 31st, 2009). The code I'm using is

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

and the error I get it

ORA-01839: date not valid for month specified

How can I use the interval keyword to be compatible with any date? Or if anyone has a better way of approaching the issue, I'm all ears.

Thank you.

Answer 1

try

select add_months(sysdate,-1) from dual

Answer 2

Being pedantic...

The requirements are not quite specified perfectly unambiguously. What does the business mean by "within the last month"? Most people would take that to mean "within the current calendar month" in which case I'd use:

TRUNC(SYSDATE,'MM')

Otherwise, perhaps they want an arbitrary period of 1 month prior to the current date - but then how do you define that? As you've found, INTERVAL '1' MONTH simply subtracts one from the month portion of the date - e.g. 15-JAN-2009 - INTERVAL '1' MONTH returns 15-DEC-1999. For some dates, this results in an invalid date because not all months have the same number of days.

ADD_MONTHS resolves this by returning the last day in the month, e.g. ADD_MONTHS(31-DEC-2009,-1) returns 30-NOV-2009.

Another possibility is that the business actually wants to use an average month period - e.g. 365/12 which is approximately 30.4. They might want you to use SYSDATE-30, although of course twelve iterations of this will only cover 360 days of the year.