Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Check constraint question...

Re: Check constraint question...

From: Knut Talman <knut.talman_at_mytoys.de>
Date: Thu, 25 Apr 2002 14:13:24 +0200
Message-ID: <3CC7F2E4.6040800@mytoys.de>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US