Cause:
When you specify to_date() function,nls_date_format parameter should support the format which you give
Most probably ‘MM-DD-YYYY‘ format works well
SQL> !oerr ORA 14120
14120, 00000, "incompletely specified partition bound for a DATE column"
// *Cause: An attempt was made to use a date expression whose format
// does not fully (i.e. day, month, and year (including century))
// specify a date as a partition bound for a DATE column.
// The format may have been specified explicitly (using
// TO_DATE() function) or implicitly (NLS_DATE_FORMAT).
Solution:
// *Action: Ensure that date format used in a partition bound for a
// DATE column supports complete specification of a date
// (i.e. day, month, and year (including century)).
// If NLS_DATE_FORMAT does not support complete
// (i.e. including the century) specification of the year,
// use TO_DATE() (e.g. TO_DATE('01-01-1999', 'MM-DD-YYYY')
// to fully express the desired date.
Check nls format for date
SQL> alter session set nls_date_format='DD-MM-YYYY';
Session altered.
SQL> show parameter nls_date_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MM-YYYY
Specify the below format when using to_date()
PARTITION BY RANGE (ORDER_DATE)
(PARTITION P_S1 VALUES LESS THAN (TO_DATE('01-01-2012','DD-MM-YYYY')),
PARTITION P_S2 VALUES LESS THAN (TO_DATE('01-01-2015','DD-MM-YYYY')),
PARTITION P_S3 VALUES LESS THAN (TO_DATE('01-01-2018','DD-MM-YYYY')),
PARTITION P_S4 VALUES LESS THAN (TO_DATE('01-01-2021','DD-MM-YYYY')),
PARTITION P_S5 VALUES LESS THAN (TO_DATE('01-01-2024','DD-MM-YYYY')),
PARTITION P_S6 VALUES LESS THAN (TO_DATE('01-01-2027','DD-MM-YYYY')),
PARTITION P_S7 VALUES LESS THAN (TO_DATE('01-01-2030','DD-MM-YYYY')),
PARTITION P_S8 VALUES LESS THAN (TO_DATE('01-01-2033','DD-MM-YYYY')),
PARTITION P_S9 VALUES LESS THAN (maxvalue));
