Re: sysdate assumes year 1900 in months_Between calculations

Hi Joe,
Two questions:
What is your current nls_date_format (you can find that in the nls_session_parameters view).
Are you sure ketstimeacctsalesman read 1999, instead of 2099?. It very much looks like 2000 gets subtracted from 2099.

Also, you might be the victim of implict conversions between character strings and dates.
I always use an explicit date format. I started this habit when Oracle, without prior notice, changed the default date format for the dutch language.

Hth,

--
Sybrand Bakker, Oracle DBA
Joe Worthington <josephwor_at_hotmail.com> wrote in message news: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:40 CST