Strange Oracle results with sysdate in query

 3 Replies
 0 Subscribed to this topic
 27 Subscribed to this forum
Sort:
Author
Messages
David Britton
Veteran Member
Posts: 53
Veteran Member
    I am getting very strange results when I use "sysdate-30" in a query. Is this a bug in Oracle? I get same results from TOAD, SQL Plus on Windows PC and SQLPlus on my (Unix) Lawson server.

    If the sysdate check is first I get incorrect results. If the sysdate check is second I get the correct results.

    Below is a sample of the query I was trying to run followed by a query that does return the correct results. The only difference is if the sysdate clause is before or after the OR statement. These queries have lots of parens , I tried with varying numbers of parens with no difference in results.


    Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

    -- Incorrect results
    SQL> select count(*) from employee emp
        where
        (emp.TERM_DATE >= (sysdate-30)) OR (emp.TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY'))
        ;

      COUNT(*)
    ----------
             5       

    --Correct results
    SQL> select count(*) from employee emp
        where
        (emp.TERM_DATE = TO_DATE('01/01/1700', 'MM/DD/YYYY')) OR (emp.TERM_DATE >=(sysdate-30))
        ;

      COUNT(*)
    ----------
          1506

    SQL>
    David@SOM
    Posts: 3
      I found this interesting as I just did a similar sql with END-DATE and sysdate.  I copied from your post and ran both your sql's, I got the same result # for each.  Can't see why you would get different results.  We are also running 11g.   
      Kwane McNeal
      Veteran Member
      Posts: 479
      Veteran Member
        I ran this also, I got the same results as David@SOM. I was using Google, and it seems you may want to look for a bug since you're on 11.2.0.1, and that's old, or if you're running in a RAC setup, could be an issue. I'm running 11.2.0.4.0
        Kwane McNeal
        Veteran Member
        Posts: 479
        Veteran Member
          Did some more digging, seems like someone ran into something very similar to you. See this URL: https://community.oracle....ead/2331662?tstart=0

          I'd get the patch levels up to date.

          Kwane