Re: ORACLE DATE IN WHERE CLAUSE PROBLEMS

In article <3soo7s$174_at_dns.crocker.com>, davor_at_crocker.com says...
>
>Can anyone explain this strange phenomenon to me ?
>
>snip
>
>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
>
>snip

I'm not positive but I think your problem is related to the numerical value of the dates you are selecting. Try using 'YYMMDD' as the date structure and then the numerical compare will be correct.

-- 
John Carlson                            Standard Disclaimer Applies.
jecarlso_at_rdyne.rockwell.com             My opinions only.
Rocketdyne Division, Rockwell Intnl.
Canoga Park, CA, USA