Here’s a simple problem that came up on the Oracle newsgroup a little while ago: how do you get the date for the last day of the previous month?
There are various ways that spring to mind but the simplest (and the first one I thought of) was – truncate the data to the start of month, then subtract one day:
select trunc(sysdate,'MM') - 1 from dual;
This is so obviously right and simple that (after running it once to make sure I had the syntax and spelling right) I probably wouldn’t even think about testing it; after all, testing has to stop somewhere! (This explains the title of the piece – it’s not meant to be ironic, there really are times when there seems to be no need to test.)
The original question hadn’t been phrased quite the way I’ve put it, though. Their code was another obvious implementation, had been running for a couple of months, and had suddenly failed with Oracle error: “ORA-01839: date not valid for month specified”. The following code is what they had written, but I’ve also included a simplified version of the same thing:
SELECT TO_DATE( TO_CHAR( LAST_DAY( SYSDATE + numtoyminterval(-1,'MONTH') ), 'YYYY-MM-DD' ), 'YYYY-MM-DD' ) FROM DUAL ; SELECT LAST_DAY( SYSDATE + numtoyminterval(-1,'MONTH') ) FROM DUAL ;
Try running it now, and there’s a very good chance you’ll get the right result – with something like a 2% chance of failure. Yet it’s so “obviously” right that it’s hard to think that it might be worth testing. We subtract one month from the current date and report the last day of the resulting month – what could be simpler ? (It was a rhetorical question but if you want a literal answer another way of saying the same thing in Oracle would be “last_day(add_months(sysdate + rownum,-1))” and that doesn’t go wrong.
There’s probably a Metalink note about this – but the problem appears when you try to subtract one month from a date in a month with more days than the previous month: the OP first hit his problem on 31st July when Oracle tried to generate the date “31st June”.
So I thought I’d write a little script to demonstrate the problem, just to show how easy it can be to test things; but when I started up an SQL*Plus session and ran the script I got an interesting result.
Imagine running the following script in early August – on what date should it fail ? I’ve shown three sets of results, which should tell you why the error message seems to appear at unexpected points:
select rownum, sysdate + rownum, last_day( (sysdate + rownum) + numtoyminterval(-1,'MONTH') ) from all_objects where rownum <= 365 ; ... rows omitted default arraysize 73 19-OCT-09 30-SEP-09 74 20-OCT-09 30-SEP-09 75 21-OCT-09 30-SEP-09 ERROR: ORA-01839: date not valid for month specified set arraysize 1 81 27-OCT-09 30-SEP-09 82 28-OCT-09 30-SEP-09 83 29-OCT-09 30-SEP-09 ERROR: ORA-01839: date not valid for month specified set arraysize 2 80 26-OCT-09 30-SEP-09 81 27-OCT-09 30-SEP-09 82 28-OCT-09 30-SEP-09 ERROR: ORA-01839: date not valid for month specified
Starting from early August you probably worked out that the query would crash on 31st October. (31st Aug goes back to 31st July, which is valid; 30th Sept goes back to 30th Aug, which is valid; 31st Oct tries to go back to 31st Sept – oops). But the first time I ran the script it seemed to crash, as you can see, on 22nd October after reporting the correct result for 21st October.
This puzzled me for a moment – until I remembered array fetching. 21st Oct is the last row of the last successful array fetch, the query crashed part-way through the next array fetch – somewhere between 22nd Oct and 14 days later. Changing the arraysize changes the apparent crash point – and given the way that SQL*Plus plays a funny little game under the covers by adding one to the specified arraysize it’s not possible to see from SQL*Plus exactly what value the server is crashing on.
It’s strange the little side effects you see, even on the simplest of tests.