Re: Uppercase constraint on Date Field
Date: 1997/03/20
Message-ID: <5grve8$5r3_at_info.csufresno.edu>#1/1
In article <19970319055301.AAA10672_at_ladder01.news.aol.com>,
LizRHW <lizrhw_at_aol.com> wrote:
>I have inherited a database with many date fields having a constraint
>forcing upper case. This would seem to have no effect since there is not
>really a case associated with a date. However...<snip>
It is due to the way the upper function treats the century part of a
date, using your NLS_DATE_FORMAT. Your Upper(Col1) probably converts
the value of Col1 to text as in: to_char(col1,'dd-mon-yy'); Then it
has to convert the text date back to internal date format to check
against col1, as in:
newdate:= to_date(to_char(col1,'dd-mon-yy'),'dd-mon-yy')
The newdate result has just taken your client machines' century part
of the date as its century, which is 19.
Now when it compares newdate to the old col1 date with year 2000, it is no longer equal.
If you change your systems NLS_DATE_FORMAT to 'DD-MON-YYYY', the problem will go away. Try the following SQL Plus script, and see:
VARIABLE Txt VARCHAR2(80);
Declare
D1 date:=to_date('01-JAN-2000','DD-MON-YYYY'); D2 date := UPPER(D1);
T1 Varchar2(12) := to_char(D1,'DD-MON-YYYY'); T2 Varchar2(12) := to_char(D2,'DD-MON-YYYY'); T3 Varchar2(10);
Begin
If D1=upper(D1) then
T3 := ' IS TRUE';
else
T3 := ' IS FALSE';
End if;
:Txt := T1||' = '||T2||T3;
End;
Received on Thu Mar 20 1997 - 00:00:00 CET