| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: search by date range
Simon Hedges wrote:
>
> <pberetta_at_my-deja.com> wrote in message news:80m4qp$26h$1_at_nnrp1.deja.com...
> > Henry,
> > Oracle does something slightly strage with date comparisons, it
> > includes the time component in the calculation when not forced not to
> > do this. Thus, by Oracle logic a record dated 01/01/1999 00:00:01
> > meets the > '01-NOV-1999' criterion. You need to either code either:
> > SELECT BLAH_DT
> > FROM BLAH
> > WHERE BLAH_DT >= TO_DATE('02-NOV-1999');
> > or
> > SELECT BLAH_DT
> > FROM BLAH
> > WHERE TRUNC(BLAH_DT) > TRUNC(TO_DATE('01-NOV-1999');
> > unless you can be absolutely certain that all records have 00:00:00
> > times components. TRUNC() eliminates the time component from the
> > comparison.
>
> And do be aware that TRUNC is a function, and so will stop indexes
> working on any dates that you have (at least this was true in Oracle 7 -
> I don't know if indexing has improved in Oracle 8 to eliminate this
> problem).
> It's far better (if you can) to store the dates without the time element.
>
> Simon Hedges
> Gloucester
> UK
as of oracle 8i, you can have indexes on functions, thus you could create an index on "trunc(dtecol)"....
However, I would suspect that a little management with the dates as they come in (say with the trigger to remove the time for example) and then indexing the date column directly would possibly yield a better result
Cheers
--
"Some days you're the pigeon, and some days you're the statue." Received on Sun Nov 14 1999 - 07:48:34 CST
 |
 |