Oracle Database INTERVAL Partitioning Date Error With 31-12-9999

You may have seen the below error when using dates at the extremes with an Oracle Database. This post focuses on the scenario where you’re inserting a high date, such as 31-12-9999 into an INTERVAL partitioned table. High dates such as this are often used in warehouse environments where from and to dates are specified to denote a period of time the data was valid for – a high date specifying the current data row.

Take the following table and partition definition:

CREATE TABLE interval_date_test
(
  my_date   DATE NOT NULL
)
PARTITION BY RANGE (my_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_date_test0 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

Each row inserted will go into a partition for that year-month combination. This can be useful for high volume data that’s generally queried for one-or-so months at a time.

If, however, you try to insert the following row, you’ll get an error:

INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31');

INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31')
            *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

This is due to the way the Oracle Database defines a partition. An INTERVAL RANGE partition in an Oracle Database is defined as accepting dates LESS THAN a specific value. The below example would store any dates up to the clock ticking over into May 2016.

VALUES LESS THAN (TO_DATE('2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

When partitioned monthly, the LESS THAN value for inserting DATE ‘9999-12-31’ would have to be DATE ‘10000-01-01’ and that is not a valid date value. Oracle Database dates must be between the year -4713 and +9999 inclusive as explained by the error.

The solution? Either use a non-INTERVAL partition strategy, reduce the date being INSERTed or experiment with a Virtual Column and partition on that.