- 2.28, the customer WeChat official account updated the bill list function: the account records found in the owners’ feedback group were not their own, and the users’ numbers were written and killed after verification and speculation. ok Working from home is a little sloppy. Let’s understand.
- On February 29, Saturday, I didn’t wake up from my lazy sleep, and the owners reported that the bill couldn’t be found. I’m angry, who forwarded the screenshot of the problem to the group communication group.
Turn around and think, no, it can be used normally after updating yesterday. This guy won’t work like this. He’ll update his work overtime. It doesn’t feel right. Check the log.
The bill list is loaded for nearly a year by default, and no time related information is delivered. How can the specified month and date be invalid. Execute the corresponding SQL directly in the background, or report an error.
Ora-01839: the date of the specified month is invalid 01839. 00000 - "date not valid for month specified"
It seems to be a problem with SQL statements, but the interface has not been updated recently. Finally locked in
sysdate - interval '1' YEAR --Of course, this problem will only appear on a specific date, such as select TO_DATE('2020-02-29','yyyy-MM-dd') - interval '1' YEAR from dual
Through online inquiry, it turns out that this problem is caused by leap year 29 and oracel’s interval processing mechanism.
Here is the alternative writing:
select add_months(TO_DATE('2020-02-29','yyyy-MM-dd'),-12) from dual
Here is a description of the problem:
It might be disappointing, but it is to be expected. [It is mentioned in the documentation](https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#i48042): When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error. For example, the next two statements return errors: SELECT TO_DATE('31-AUG-2004','DD-MON-YYYY') + TO_YMINTERVAL('0-1') FROM DUAL; SELECT TO_DATE('29-FEB-2004','DD-MON-YYYY') + TO_YMINTERVAL('1-0') FROM DUAL; The first fails because adding one month to a 31-day month would result in September 31, which is not a valid date. The second fails because adding one year to a date that exists only every four years is not valid.
Oracle official documents have said: interval calculation returns a datetime value, the return value must be an accurate datetime value, otherwise an error is returned.
To put it bluntly, after interval calculation, Oracle will not do the conversion. For example, in the first case, if there is no 31, it will not help you go to the 1st of the next month; in the second case, if there is no 29, it will not help you go to the 1st of the next month or the 20th of this month. Because it’s not accurate.
Finally, I sigh that I have encountered a bug once in four years!!!