CHECK CONSTRAINT FECHA

vote3down votefavorite

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.   share|improve this question edited Feb 5 '12 at 19:02   Hubert Kario

3 Answers

 

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;

FECHA FORMATO DDSPTH

SQL> SELECT TO_CHAR(SYSDATE,'MONTH DDSPTH') from dual;
TO_CHAR(SYSDATE,'MONTHDDSPTH')
------------------------------------------------------------------------
OCTOBER   THIRD
SQL> SELECT TO_CHAR(SYSDATE,'YEAR MONTH DDSPTH') from dual;
TO_CHAR(SYSDATE,'YEARMONTHDDSPTH')
--------------------------------------------------------------------------------
TWENTY THIRTEEN OCTOBER   THIRD
SQL> SELECT TO_CHAR(SYSDATE,'YEAR MONTH DAY DDSPTH') from dual;
TO_CHAR(SYSDATE,'YEARMONTHDAYDDSPTH')
--------------------------------------------------------------------------------
TWENTY THIRTEEN OCTOBER   THURSDAY  THIRD