How to check if time is greater or less than another time in PLSQL

Question

I am trying to check in my trigger if an inserted record is in the table TUTPRAC contains and CLASSTIME < '9AM' OR > '6PM'. If this is true then that record is updated with certain fields being changed to NULL.

CREATE TRIGGER CheckBeforeAfterHours
BEFORE INSERT OR UPDATE OF CLASS_TIME ON TUTPRAC
FOR EACH ROW
BEGIN
  IF (:NEW.CLASS_TIME < '09:00' OR > '18:00') THEN
     :NEW.STAFFNO := NULL;
     :NEW.CLASS_DAY := NULL;
     :NEW.CLASS_TYPE := NULL;
     :NEW.ROOMNUM := NULL;
  END IF;
END CheckBeforeAfterHours;

Columns of TUTPRAC table:

CLASSID (PK), UNITCODE, STAFFNO, CLASSDAY, CLASSTIME, CLASSTYPE, ROOMNUM

The field CLASSTIME is set to varchar(5).

I use Oracle SQLDeveloper.

PROBLEM

my issue is that i keep getting this error when i try to run the trigger:

Error(2,36):
PLS-00103: Encountered the symbol ">" when expecting one of the following:
    ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> continue avg count current exists max min prior sql stddev
    sum variance execute forall merge time timestamp interval
    date <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe
    <an alternatively-quoted string literal with character set specification>
    <an alternatively

Answer 1

There are two issues here:

IF (:NEW.CLASS_TIME < '09:00' OR > '18:00')

The syntax is incorrect. You need to mention :NEW.CLASS_TIME in the OR condition too.

The field "CLASSTIME" is set to varchar(5)

Then you should go for numeric comparison rather than string comparison. String comparison is based on the fixed format, which does a comparison based on the ASCII values and not the pure number.

Let's say if you pass 5:00 instead of 05:00, i.e. when the format is not fixed, then the comparison will give different outputs because the ASCII values would be different.

SQL> SELECT ascii('05:00'), ascii('5:00') FROM dual;

ASCII('05:00') ASCII('5:00')
-------------- -------------
            48            53

Setup

SQL> CREATE TABLE t(A VARCHAR2(5));

Table created.

SQL> INSERT INTO t VALUES('04:00');

1 row created.

SQL> INSERT INTO t VALUES('05:00');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM t;

A
-----
04:00
05:00

String comparison

SQL> SELECT * FROM t WHERE a < '5:00';

A
-----
04:00
05:00

SQL> SELECT * FROM t WHERE a < '05:00';

A
-----
04:00

So what happened above? '05:00' and '5:00' are not same. To avoid this confusion, better go for numeric comparison.

SQL> SELECT * FROM t WHERE TO_NUMBER(SUBSTR(a, 2, 1)) < 5;

A
-----
04:00

SUBSTR will extract the numeric part and TO_NUMBER will explicitly convert it into number.

Answer 2

The correct syntax is:

IF (:NEW.CLASS_TIME < '09:00' OR :NEW.CLASS_TIME > '18:00') THEN