Search notes:

ORA-02436: date or system variable wrongly specified in CHECK constraint

In the following create table statement, the to_date of the check constraint does not have conversion format and it is not clear which format should be chosen.

Oracle won't create the table with such a constraint and throws ORA-02436: date or system variable wrongly specified in CHECK constraint.

create table tq84_ORA_02436 (
   dat   varchar2(10) not null check(to_date(dat) >= date '2020-01-01'),
   val   number(5,1)
);

Setting event 10149

If someone insists on creating the table, the event 10149 can be set:

alter session set events '10149 trace name context forever, level 1';

create table tq84_ORA_02436 (
   dat   varchar2(10) not null check(to_date(dat) >= date '2020-01-01'),
   val   number(5,1)
);

However, the following sequence throws ORA-01843: not a valid month

alter session set nls_date_format = 'dd/mm/yyyy';
insert into tq84_ORA_02436 values ('28/08/2021', 42);

ORA-02436: date or system variable wrongly specified in CHECK constraint

Setting event 10149

See also