date - add_months function strange behaviour in Oracle 19c

Question

I've just bumped into very strange case for me. See:

select add_months(to_date('26.01.2021 01:04:00', 'DD.MM.YYYY HH24:MI:SS'), 500000) good from dual;
-- returns 3751-09-26 01:04:00

select add_months(to_date('26.01.2021 01:04:00', 'DD.MM.YYYY HH24:MI:SS'), 100000) bad from dual;
-- returns ORA-01841 (full) year must be between -4713 and +9999, and not be 0

Okay, let's check: 500000 months is about 41666 years which is already more than +9999 bound. Is it somehow cycling inside? As for 100000 months = 8333 years - it is inside of (-4713;+9999) interval but sum of 2021 year and 8333 years is not.

I'm a bit confused.

Oracle version is:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

question from:https://stackoverflow.com/questions/65905212/add-months-function-strange-behaviour-in-oracle-19c

Answer 1

Waitting for answers