CODEWITHSUNDEEP
oracle-databasedate
Riot
Riot

Reputation: 63

Adding one day to a date in Oracle

I have looked at similar threads but nothing seems to have my answer. Here is what I have. 

FDTMDUEDATE+1   >= Rundate

It brings me this error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

If I do 

FDTMDUEDATE-1  >= Rundate

It will work without issue. Any help would be great, thanks

Upvotes: 2

Views: 29026

Answers (1)

GMB
GMB

Reputation: 222462

Adding 1 to an Oracle date does work as expected, ie it does add one day to the date :

SELECT TO_DATE('2019-01-29', 'yyyy-mm-dd' ) + 1 tomorrow FROM DUAL;

| TOMORROW  |
| :-------- |
| 30-JAN-19 |

However, as explained in the Date Data Types documentation :

Valid date range from January 1, 4712 BC, to December 31, 9999 AD.

In your use case it is likely that you are hitting the upper limit, as demonstrated in this statement :

SELECT TO_DATE('9999-12-31', 'yyyy-mm-dd' ) + 1 out_of_range FROM DUAL;

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Adding 1 day to '9999-12-31' causes the date to go out of the allowed range, causing error ORA-01841.

It does not look like a normal situation that you are processing such far future dates, and you would probably need to investigate why.

In the meantime, you should be able to workaround the issue by rewritting this :

FDTMDUEDATE + 1 >= Rundate

To :

FDTMDUEDATE >= Rundate - 1

(Unless, of course, Rundate is equal to January 1, 4712 BC...)

Upvotes: 2

Related Questions