I need to create a table, where the values of column "ttime
" can be within the range of 10:00
and 22:00
. Here' 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 wrongly specified in CHECK constraint
How can I avoid it? What's the problem?
To enforce such a constraint you need to extract time from
ttime
column. When you convert a time portion of a date(represented as a string literal here'10:00'
) to a value ofDATE
data type the date portion defaults to the first day of the current month and year, thusTO_DATE('10:00', 'HH24:MI')
will result in01/05/2017 10:00:00
and your condition becomesNote:
AND
should be replaced byOR
.ttime
cannot be less than some value and at the same time be greater than the same value.Having said that, as one of the options, you can define your constrains as follows: