Re: Check constraint question...

[Quoted] Yes, we did learn this a couple years ago...but for my purposes, YY would have worked fine, so I left it....But as your post forced me into remembering....I changed it to YYYY and it worked...Guess 9i is forcing us to by compliant! :-)

Thanks for the help!!!!!!!!

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:aa8rtj0h8a_at_drn.newsguy.com...
> In article <g_Kx8.1193$3u4.426545303_at_newssvr13.news.prodigy.com>, "CJ
says...
> >
> >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
> >
> >
> >
>
>
> is that 1902, 2002 or 2102 or year 2???
>
> a simple configuration change in the init.ora or a change in date would
change
> the meaning of the constraint.
>
>
> never never never rely on implicit conversions and always always always
(thought
> we all learned this 2 years ago) use 4 digit years.
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t
> 2 ( x date,
> 3 constraint x_check check (x between
TO_DATE('01-MAY-2002','DD-MON-YYYY')
> 4 and
TO_DATE('31-MAY-2002','DD-MON-YYYY'))
> 5 );
>
> Table created.
>
> --
> Thomas Kyte (tkyte_at_oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Thu Apr 25 2002 - 17:56:40 CEST