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
>
>
> Thanks for any help
>
>
> CJ
>
>
>Hi, Your check string is of type CHAR/VARCHAR2, not comparable with date.

Try ( trunc(activitydate) between to_date(''01-MAY-02','dd-mon-rr')

                           and     to_date('31-MAY-02', 'dd-mon-rr'))

The wise developers include the date format, NLS_DATE_FORMAT can be changed on the server, blowing your sql out of the sky.

/Svend
Svend_at_OracleCare.Com Received on Thu Apr 25 2002 - 11:43:24 CDT