Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Check constraint question...
CJ Butcher wrote:
> I'm trying to create a check constaraint and get error stating:
>
> ORA-02436: date or system variable wrongly specified in CHECK constraint
>
> Here is my constraint statement:
> ALTER TABLE ACTIVITYTRACKING200205 ADD CONSTRAINT ACTIVITY200205
>
> CHECK (ACTIVITYDATE BETWEEN '01-MAY-02' AND '31-MAY-02');
>
> ACTIVITYDATE is defined as a DATE type. Don't understand why I'm getting
> the error. I'm using Oracle 9i
You're getting the error because you didn't specifiy a date, you specified a string. Just use TO_DATE to correct your constraint.
ALTER TABLE ACTIVITYTRACKING200205 ADD CONSTRAINT ACTIVITY200205 CHECK (ACTIVITYDATE BETWEEN TO_DATE('01-MAY-02','DD-MON-YY') AND TO_DATE('31-MAY-02','DD-MON-YY')); And make sure all data in ACTIVITYTRACKING200205 does not violate the new constraint.
Regards,
Knut Received on Thu Apr 25 2002 - 07:13:24 CDT