Re: ORACLE DATE IN WHERE CLAUSE PROBLEMS

From: Mike Rife <rife_at_aarlo.moffitt.usf.edu>
Date: 1995/06/29
Message-ID: <3sum3e$m4i_at_mother.usf.edu>#1/1


In article <3soo7s$174_at_dns.crocker.com>, davor_at_crocker.com (Davor Saric) says:
>
>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
>

A DATE column has the date and TIME. Have you taken into consideration that the times are stored in the database too? This would affect results since you did not reference the times. The default time is '00:00:00'. This demonstrates it:

     SQL> l
       1  select to_char(to_date('01-JAN-95','DD-MON-YY'),'DD-MON-YY HH24:MI:SS')
       2* from dual
     SQL> /

     TO_CHAR(TO_DATE('01-JAN-95','DD-MON-YY'),'DD-MON-YYHH24:MI:SS')
     ---------------------------------------------------------------------------
     01-JAN-95 00:00:00

     SQL>
Received on Thu Jun 29 1995 - 00:00:00 CEST

Original text of this message