Re: Help with Date comparisons

Check your NLS_DATE_FORMAT to see if it is different. Did the program work before Y2K? Did it stop working on/after Jan 1? Are both databases same release and on same platform and same OS version? Also, try "between" to search for a date range (i.e., July 1999). Try using 'RR' and two digit year. Change format mask to say, '1999-07-01' to see if same error occurs. In short, make sure that Oracle knows how to parse your date format. Good Luck.

Larry Pettit wrote:

> I'm getting the following error and can't figure what's happening. It goes
> across a database
> link.
>
> SQL> select * from vm_clist
> 2 where
> 3 INVOICE_DATE >= to_date('07/01/1999','mm/dd/yyyy')
> 4 AND INVOICE_DATE < to_date('08/01/1999','mm/dd/yyyy')
> 5
> SQL> /
> INVOICE_DATE >= to_date('07/01/1999','mm/dd/yyyy')
> *
> ERROR at line 3:
> ORA-02070: database NEWJDE does not support some function in this context
>
> Here's some background information.
>
> There are three databases.
>
> Database A
> Database NEWJDE, new copy from a customer
> Database JDE, old copy from a customer
>
> Something has changed in the NEWJDE database that won't allow a date
> comparison in
> a view on a Julian date. If I take the f42119 table which contains the
> invoice date
> column and copy the data to JDE, it works fine.
>
> The above sql statement was executed in Database A on the following view.
>
> create or replace view vm_clist
> as
> select distributor_branch_seq,
> jde_distributor_seq,
> to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,
> sdlitm product_number,
> sdapum uom,
> sdsoqs quantity
> from
> scor.m_distributor_branch,
> proddta.f42119_at_newjde.ores.ps.net,
> proddta.f0101_at_newjde.ores.ps.net
> where abac04 = 'C' and
> jde_distributor_seq = aban8 and
> sdivd <> 0 and
> sdshan = aban8;
>
> If I run the following script in NEWJDE, it works fine. There's just
> something that changed
> in the newjde date format.
>
> select
> to_date(to_char(1900000 + sdivd),'yyyyddd') invoice_date,
> sdlitm product_number,
> sdapum uom,
> sdsoqs quantity
> from
> proddta.f42119,
> proddta.f0101
> where abac04 = 'C' and
> sdivd <> 0 and
> sdshan = aban8 and
> to_date(to_char(1900000 + sdivd),'yyyyddd') >=
> to_date('07/01/1999','mm/dd/yyyy')
> AND to_date(to_char(1900000 + sdivd),'yyyyddd') <
> to_date('08/01/1999','mm/dd/yyyy')
>
> Any suggestions?
>
> Thanks for your help.

Received on Wed Jan 12 2000 - 01:12:40 CST