Re: sysdate assumes year 1900 in months_Between calculations

I have faced the same problem on 2 notebooks. One with PO815 and 1 with PO804
To my opinion sysdate is correct (2000), but Oracle "sees" ketsTimeAcctSalesman as 2099. (probably these data are inserted as dd-mon-yy)
The problem on the PO815 notebook seems to be solved by setting the the registry NLS_DATE_FORMAT='DD-MON-RR" (installation carried out in1999) On the PO804 it's still not working (Installed in 2000).

EG: Table EMP in demobld.sql (scott/tiger) on the PO815 notebook (input in 'dd-mon-yy' format in demobld.sql)
SQL> select months_between(sysdate,hiredate) from emp;

MONTHS_BETWEEN(SYSDATE,HIREDATE)

                       229.60562
                       227.50884
                       227.44433
                       226.08949
                       220.25078

SQL> select to_char(hiredate,'dd-mon-rrrr') from emp; (or yyyy )

TO_CHAR(HIR

17-dec-1980
20-feb-1981
22-feb-1981
02-apr-1981
28-sep-1981
01-may-1981

If I'm carrying out the same queries on PO804 I get A: negative figures for months_between and B: 2080 instead of 1980, etc.

(I'm not a DBA'er, but in case of notebooks we've to figure it out ourselves)

Regards

Martin Vonk

Joe Worthington <josephwor_at_hotmail.com> schreef in berichtnieuws 87es7q$k1v$1_at_nntp9.atl.mindspring.net...
> I am comparing sysdate (now 2000) to a date field. Oracle is assuming that
> sysdate is 1900. See code below.
>
> select
> ketsTimeAcctSalesman,
> sysdate System_Date,
> ROUND(MONTHS_BETWEEN(Sysdate, ketsTimeAcctSalesman),0)
> Time_Assigned_to_Salesman
> FROM KETS_ACCT_PROFILE
> where (ROUND(MONTHS_BETWEEN(Sysdate, ketsTimeAcctSalesman),0) < 0);
>
> This code produces the following.
>
> KETSTIMEACCTSALESMAN SYSTEM_DATE TIME_ASSIG
> ------------------------------ -------------------- ----------
> 15-SEP-99 04-FEB-00 -1195
> 05-OCT-99 04-FEB-00 -1196
> 03-FEB-99 04-FEB-00 -1188
> 01-FEB-96 04-FEB-00 -1152
> 02-APR-98 04-FEB-00 -1178
> 01-JAN-99 04-FEB-00 -1187
> 01-JAN-99 04-FEB-00 -1187
>
> Any Ideas ?
>
>
>
>
Received on Fri Feb 04 2000 - 12:08:26 CST