Re: search by date range

From: Simon Hedges <shedges_at_hhhh.freeserve.co.uk>
Date: Sun, 14 Nov 1999 11:37:22 -0000
Message-ID: <80m6p3$520$2@news8.svr.pol.co.uk>

<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 Received on Sun Nov 14 1999 - 05:37:22 CST