Using date in a check constraint, Oracle

I am trying to check add the following constraint but Oracle returns the error shown below.

ALTER TABLE Table1
ADD (CONSTRAINT GT_Table1_CloseDate
CHECK (CloseDate > SYSDATE),
CONSTRAINT LT_Table1_CloseDate
CHECK (CloseDate <= SYSDATE + 365)),
CONSTRAINT GT_Table1_StartDate
CHECK (StartDate > (CloseDate + (SYSDATE + 730))));

Error:

Error report:
SQL Error: ORA-02436: date or system variable wrongly specified in CHECK constraint
02436. 00000 -  "date or system variable wrongly specified in CHECK constraint"
*Cause:    An attempt was made to use a date constant or system variable,
           such as USER, in a check constraint that was not completely
           specified in a CREATE TABLE or ALTER TABLE statement.  For
           example, a date was specified without the century.
*Action:   Completely specify the date constant or system variable.
           Setting the event 10149 allows constraints like "a1 > '10-MAY-96'",
           which a bug permitted to be created before version 8.

Solution 1:

A check constraint, unfortunately, cannot reference a function like SYSDATE. You would need to create a trigger that checked these values when DML occurs, i.e.

CREATE OR REPLACE TRIGGER trg_check_dates
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  IF( :new.CloseDate <= SYSDATE )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
          'Invalid CloseDate: CloseDate must be greater than the current date - value = ' || 
          to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.CloseDate > add_months(SYSDATE,12) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
         'Invalid CloseDate: CloseDate must be within the next year - value = ' || 
         to_char( :new.CloseDate, 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
  IF( :new.StartDate <= add_months(:new.CloseDate,24) )
  THEN
    RAISE_APPLICATION_ERROR( -20002, 
          'Invalid StartDate: StartDate must be within 24 months of the CloseDate - StartDate = ' || 
          to_char( :new.StartDate, 'YYYY-MM-DD HH24:MI:SS' ) ||
          ' CloseDate = ' || to_char( :new.CloseDate , 'YYYY-MM-DD HH24:MI:SS' ) );
  END IF;
END;

Solution 2:

You cannot use SYSDATE in check constraint. According to documentation

Conditions of check constraints cannot contain the following constructs:

• Subqueries and scalar subquery expressions
• Calls to the functions that are not deterministic (CURRENT_DATE,
  CURRENT_TIMESTAMP, DBTIMEZONE,
  LOCALTIMESTAMP, SESSIONTIMEZONE,
  SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV)
• Calls to user-defined functions
• Dereferencing of REF columns (for example, using the DEREF function)
• Nested table columns or attributes
• The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
• Date constants that are not fully specified

For 10g Release 2 (10.2), see constraint, and for 11g Release 2 (11.2) see constraint.

Remember that an integrity constraint is a statement about table data that is always true.

Anyway: I don't know exactly what you are trying to achieve but I think you can use triggers for this purpose.

Solution 3:

Each and every time the record is updated SYSDATE will have a different value. Therefore the constraint will validate differently each time. Oracle does not allow sysdate in a constraint for that reason.

You may be able to solve your problem with a trigger that checks if CloseDate has actually changed and raise an exception when the new value is not within range.

And: What is (StartDate > (CloseDate + (SYSDATE + 730))))? You cannot add dates.

And: StartDate needs to be after CloseDate? Is that not weird?