Monday, March 06, 2023

The strange case of ORA-01841 and ADD_MONTHS

Today I came across this strange behavior of the function ADD_MONTHS:
if I add a number of months between 1 and 11 to the maximum allowed date, that is December 31, 9999, I get NULL as result (!).
But if I add 12 or more, I get ORA-01841 error.

SELECT ADD_MONTHS(date'9999-12-31', 1) d FROM DUAL;

D
======
(null)
SELECT ADD_MONTHS(date'9999-12-31', 11) d FROM DUAL;
D
======
(null)


SELECT ADD_MONTHS(date'9999-12-31', 12) d FROM DUAL;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range

 

Oddly enough, if I add just 1 day, it will raise the error . 

SELECT date'9999-12-31' + 1 d FROM DUAL;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0 01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0" *Cause: Illegal year entered *Action: Input year in the specified range

In my humble opinion, returning NULL in the aforementioned cases makes no sense at all.

This problem occurs also on Oracle 19c (Oracle 19.18.0.0.0) and Oracle 21c XE (21.3.0.0.0).

See message translations for ORA-01841 and search additional resources.

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles