ORACLE DATE IN WHERE CLAUSE PROBLEMS

From: Davor Saric <davor_at_crocker.com>
Date: 1995/06/27
Message-ID: <3soo7s$174_at_dns.crocker.com>#1/1

Can anyone explain this strange phenomenon to me ?

I have an unhappy little database where I have defined a table called ORDERS, and this unhappy little table has a field called OR_BDATE defined as type DATE. My unhappy little table has 97,382 records, of which 97381 of them have an OR_BDATE field which is NOT NULL. Of these 97K records there are 384 distinct OR_BDATE values, the minimum of which is 03-JAN-94, and the maximum of which is 26-JUN-95, as evidenced by the following piece of SQL:

   SQL> select min(or_bdate) , max(or_bdate) from orders;

   MIN(OR_BD MAX(OR_BD

• --------- 03-JAN-94 26-JUN-95

NOW, when I ask my uncooperative ORACLE 7 server how many records have an OR_BDA TE
between 03-JAN-94 and 26-JUN-95, it tells me I only have 20. As shown below:

   SQL> l

     1     select
     2        count(*)
     3     from
     4        orders
     5     where
     6              or_bdate > to_date('01-03-94','MM-DD-YY')
     7        AND   or_bdate < to_date('06-26-95','MM-DD-YY')
     8*

   SQL> /      COUNT(*)

---

           20

   SQL> What's more, if I have the gaul to use the BETWEEN...AND... operator, my charming little ORACLE SERVER tells me I have 25 matches, as seen here:

   SQL> l

     1  select
     2        count(*)
     3     from
     4        orders
     5     where
     6              or_bdate between  to_date('01-03-94','MM-DD-YY')
     7*       AND                     to_date('06-26-95','MM-DD-YY')

   SQL> /      COUNT(*)

---

           25

   SQL> I find the whole thing quite perplexing. If anyone has any idea what's causing these strange,
and counter-intuitive results please let me know, because I'm at whits end.

I see no reson for the between, and TO_DATE(...) combination not to work.

Also, can anyone tell me if this is the best (performance wise) way to test for a date in
a range.

Thanks,

davor_at_crocker.com Received on Tue Jun 27 1995 - 00:00:00 CEST