Problem with time

[Jphillis]

I have a start and a finish time. I want to ensure that the end user cannot insert a finish time that is less than the start time.
eg
Start time 10:50 Finish time 10:40
I want to make it either impossible to do the above or to bring up an exception, which ever is easiest.

 

[karluk]

The easiest way to do this is to define a check constraint on your table.

alter table your_table add constraint check_times check (start_time < finish_time);

Then if your data violates this constraint, the update or insert will fail with an ORA-02290: check constraint (CHECK_TIMES) violated.

There may be issues with null values, if either of these columns allows nulls.

 

[Jphillis]

I have inserted the line of code as follows 'CONSTRAINT check_times check (start_time < finish_time)', however i get an error saying 'missing or invalid operation' what am i doing wrong?

 

[Guest_imported]

The solution you're trying is OK.
But the command you've mentioned is not complete.
Syntax can be reviewed in SQL*PLUS Manual.

Syntax will look like the following, but without the quotes
'ALTER TABLE your_table ADD CONSTRAINT check_times check (start_time < finish_time);'


P.S. Oracle stores dates with hours, minutes & seconds.

 

[lewisp]

Are the start_time and finish_time DATE columns? I assume they are. To enter a time into a date column you have to do something like:

INSERT INTO table (
start_time,
finish_time )
VALUES (
To_Dateuser_start_time,'HH24:MI'),
To_Dateuser_finish_time,'HH24:MI') );

If the colums are VARCHAR2, then be aware that '10:30' is greater than '09:30', but '10:30' is less than '9:30' in a varchar column.