Oracle constraint - compare two string like two times

Question

I would like to create table witj constrain which compare two varchar2(8 char) columns as time. Is it possible ?

I've made somethink like this, but it doesn't work :(

CONSTRAINT "my_constraint" CHECK (to_number(to_char(to_date(window_stop, 'hh24:mi:ss'), 'sssss')) > to_number(to_char(to_date(window_start, 'hh24:mi:ss'), 'sssss'))) ENABLE

Thx for all help. Paul.

Answer 1

Your constraint will get "ORA-02436: date or system variable wrongly specified in CHECK constraint" because if you don't provide the date elements then to_date() defaults to the first day of the current month; therefore the result of evaluating the constraint check could change after the data is inserted, which isn't allowed. It can't actually change in this specific case, but a general rule is being applied, causing the error.

You could use a nominal fixed date instead:

CONSTRAINT "my_constraint"
  CHECK (
      to_number(to_char(to_date('2000-01-01 ' || window_stop, 'YYYY-MM-DD hh24:mi:ss'), 'sssss'))
    > to_number(to_char(to_date('2000-01-01 ' || window_start, 'YYYY-MM-DD hh24:mi:ss'), 'sssss'))
  ) ENABLE

db<>fiddle

There isn't much point converting to a number though, just compare the dates:

CONSTRAINT "my_constraint"
  CHECK (
      to_date('2000-01-01 ' || window_stop, 'YYYY-MM-DD hh24:mi:ss')
    > to_date('2000-01-01 ' || window_start, 'YYYY-MM-DD hh24:mi:ss')
  ) ENABLE

db<>fiddle

Or - if you are confident that values are always going to be valid times and will have leading zeros - just compare the strings:

CONSTRAINT "my_constraint" CHECK (window_stop > window_start) ENABLE

db<>fiddle

You could also store the times as nominal dates, or as number of seconds past midnight, or as an interval, which would make it easier to prevent completely invalid values being used (e.g. '99:00:00'). But you may have a real date you can use instead - depends if these are tied to real dates, or e.g. shift patterns, or similar - which would allow you to handle windows crossing midnight. (What you should not do is store a related date and time as separate fields, but no indication that is what you are doing here.)