ORA-01841: (circumference) year must be a non-zero number between -4713 and +9999

ORA-01841: (circumference) year must be a non-zero number between -4713 and +9999

If you issue the following SQL in Oracle, it will not be an error even if it is an impossible date.

SELECT TO_DATE('99991231','YYYYMMDD') FROM DUAL

However, a +1 to this will result in the error “ORA-01841: (circumference) year must be a non-zero number between -4713 and +999999.

SELECT TO_DATE('99991231','YYYYMMDD')+1 FROM DUAL

This seems to be because the date range is from -4713 to 9999. Therefore, when designing, you should be concerned about the handling of 99991231.

For example, let’s say you have a table with the start and end dates of a telephone contract.

When you sign up for a phone contract, the contract start date is included, but what about the contract end date? If you decide to use NULL, you need to consider NULL, and if you decide to use 99991231, you need to consider the fact that +1 will cause an error in this case.

Personally, I think 29991231 is fine. I think it is a good idea. If you +1 this date, it will only be 30000101.

コメント

タイトルとURLをコピーしました