Re: ORACLE DATE IN WHERE CLAUSE PROBLEMS

Try something like this

select count(*) from orders
where
to_char(or_bdate,'yymmdd') BETWEEN '940103' AND '950626' ;

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