It may be just a little more subtle than that.
Unless things have changed since v7, when you
specify a date in a check constraint it has to
include a full four-digit year, otherwise the check
constraint could change it's meaning at the
turn of the century (or half-century depending
on your default date format).
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Knut Talman wrote in message <3CC7F2E4.6040800_at_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 - 08:12:10 CDT
