Re: *Need Help* Comparing Dates in Oracle 8

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Sep 1999 14:48:41 -0400
Message-ID: <ca=zN4hoVCq4LU33qLhAdsHZ8DwC@4ax.com>

A copy of this was sent to Mark MacRae <mNOmSPAM_at_witty.com.invalid> (if that email address didn't require changing) On Thu, 30 Sep 1999 10:24:56 -0700, you wrote:

>Hi, I posted the other day, and got no response - I can't
>believe that nobody else has run into this, so I'm posting
>again. Since upgrading my Oracle client from 7.3 to 8.x,
>the following SQL statement no longer works:
>SELECT E1.REQUEST FROM ID_ENTRY_1 E1 WHERE E1.SUBMIT_DATE
>>= to_date('1999-2-12 12:12:12 PM', 'YYYY-MM-DD HH:MI:SS
>PM')
>
>The error I get is "invalid comaparison operator". So it
>would seem that there is either a new way to compare dates
>in Oracle 8 which isn't documented (as far as I can tell),
>or there's a bug in the client. Whichever it is, would
>somebody please let me know. Thanks.
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!

it is valid syntax..

scott_at_8.0> select ename from emp where hiredate >=   2 to_date( '1999-2-12 12:12:12 PM', 'YYYY-MM-DD HH:MI:SS PM' )   3 /

no rows selected

scott_at_8i> select ename from emp where hiredate >=   2 to_date( '1999-2-12 12:12:12 PM', 'YYYY-MM-DD HH:MI:SS PM' )   3 /

no rows selected

so -- that is in 8.0 and 8.1. It must be the client.

have you tried the query in SQLPLUS directly?

If it fails in sqlplus -- post a cut and paste of the session with the query visible and the error message. If it does work -- its the client.

If it is the client, can you enable sql_trace in the client. For example -- in SQLPLUS I issued:

tkyte_at_8.0> alter session set sql_trace=true;

Session altered.

tkyte_at_8.0> select * from emp where x !> y; select * from emp where x !> y

                           *

ERROR at line 1:
ORA-00927: missing equal sign

that will create a trace file on the server. This trace file shows me:

....
STAT #2 id=1 cnt=7 pid=0 pos=0 obj=0 op='FIXED TABLE FULL X$NLS_PARAMETERS^C\244<^A^C\244<
 '

---

PARSE ERROR #1:len=32 dep=0 uid=10991 oct=3 lid=10991 tim=42912201 err=927 select * from emp where x !> y
XCTEND rlbk=0, rd_only=1

that is -- it shows the query that the client actually submitted to the server. If the client is using odbc or some other 'magic' -- it may very well be rewriting the query and you aren't actually submitting the query you believe you are. The trace file will show you the query.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 30 1999 - 13:48:41 CDT