Re: ORACLE DATE IN WHERE CLAUSE PROBLEMS

In article <3soo7s$174_at_dns.crocker.com> davor_at_crocker.com (Davor Saric) writes:

> 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.

The most obvious difference you have in the two selects is that between..and is inclusive. That's why you have 20 records in the exclusive range (using < and > operands) and 25 records in the inclusive range (using between...and operands); you simply have 5 records that fall right on '01-03-94' and '06-26-95'.

As for the huge discrepancy in the number of records, I would say your unhappy little insert/update statements screw up your date format and the minimum date is actually Jan 03, 0094 instead of Jan 03, 1994 (though both will show up as '03-JAN-94' when using 'DD-MON-YY'). Use a four-digit year format to verify.

> 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.

Among other things, I suggest you should change (either locally or globally) NLS_DATE_FORMAT to 'MM-DD-YY'. I promise you won't get fined in using a different date format other than Oracle's default. :)

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel