CODEWITHSUNDEEP
oracle-databasecheck-constraint
Ольга Сергеева
Ольга Сергеева

Reputation: 31

Check constraint for column in SQL Developer

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?

Upvotes: 3

Views: 868

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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 of DATE data type the date portion defaults to the first day of the current month and year, thus TO_DATE('10:00', 'HH24:MI') will result in 01/05/2017 10:00:00 and your condition becomes 

ttime >= 01/05/2017 10:00:00 and  ttime <= 01/05/2017 22:00:00

Note: AND should be replaced by OR. 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:

constraint t1_chk01 check (to_number(to_char(c1, 'HH24MI')) 
                           between 1000 and 2200)

Upvotes: 3

Related Questions