ORA-14120: incompletely specified partition bound for a DATE column

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));

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s