Re: ORACLE DATE IN WHERE CLAUSE PROBLEMS

> In article <3soo7s$174_at_dns.crocker.com> Davor Saric writes:
> Can anyone explain this strange phenomenon to me ?

1. "x BETWEEN a AND b" includes the endpoints a and b.
2. "x > a AND x < b" does not include the endpoints a and b.
3. Oracle DATE datatype includes a time. When you do not explicitly include the time, you are implicitly specifying a time of 00:00:00, the first second of the day.

> 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

Retrieves 20 rows from 01-03-94 00:00:01 to 06-25-95 23:59:59, inclusive.

> 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

Retrieves 25 rows from 01-03-94 00:00:00 to 06-26-95 00:00:00, inclusive.

So 5 rows must fall on the endpoints 01-03-94 00:00:00 or 06-26-95 00:00:00.

And 97,356 (97381-25) rows must be outside the upper endpoint, but still some time on 06-26-95?

Is your database/table a bit happier now? Does your server seem any more cooperative?

>
> 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 Wed Jun 28 1995 - 00:00:00 CEST