Re: Check constraint question...

Hi
...
TO_DATE('31-MAY-2002','DD-MON-YYYY','NLS_DATE_LANGUAGE = American') ...

Stax

Thomas Kyte wrote:

> 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 - 18:30:45 CEST