Oracle interval ‘1’ year reported an error today

Time:2021-2-24
  • 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.

API网关日志

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.

Query containing SYSDATE – INTERVAL ‘1’ YEAR fails for today’s date(29th February 2016)

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.

It means:

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!!!

Recommended Today

Practice analysis of rust built-in trait: partialeq and EQ

Abstract:Rust uses traits in many places, from simple operator overloading to subtle features like send and sync. This article is shared from Huawei cloud community《Analysis of rust built-in trait: partialeq and EQ》Author: debugzhang Rust uses traits in many places, from simple operator overloading to subtle features like send and sync. Some traits can be automatically […]