Re: Check constraint question...

CJ Butcher wrote:

> 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
> >

Of course if those two digits were a big deal you could likely use RR.

Daniel Morgan Received on Thu Apr 25 2002 - 18:48:35 CEST