Check constraint for a column in SQL Developer
I need to create a table where the column values " ttime
" can be in the range 10:00
and 22:00
. Here is the code:
create table test (
ttime date,
CONSTRAINT CHECK_ttime CHECK(ttime >= TO_DATE('10:00', 'HH24:MI') AND
ttime <= TO_DATE('22:00', 'HH24:MI'))
);
But when I create this table, an error occurs:
ORA-02436: date or system variable incorrectly specified in CHECK constraint
How can I avoid this? What is the problem?
source to share
To enforce this constraint, you need to extract the time from the column ttime
. When you convert a time part of the date (represented as a string literal here '10:00'
) to a value type DATE
, the default date is the first day of the current month and year, so that TO_DATE('10:00', 'HH24:MI')
in 01/05/2017 10:00:00
and your condition will be
ttime >= 01/05/2017 10:00:00 and ttime <= 01/05/2017 22:00:00
Note: AND
should be replaced with OR
. ttime
cannot be less than a certain value and at the same time be more than one and the same value.
Having said that, as one option, you can define your constraints like this:
constraint t1_chk01 check (to_number(to_char(c1, 'HH24MI'))
between 1000 and 2200)
source to share