Re: search by date range

Right Simon,

   That's why I showed both versions, but I forgot to mention the reason you may want to use date >= next day. If the time element is unimportant, a database trigger to set all the dates to midnight is probably the most sure-fire way to insure that nobody will get creative and foul up the works. Once this is in place and a one shot update to bring all existing rows into compliance has been done, then > cutoff date will work 100%. Still can't figure out what the folks at Oracle were thinking (or perhaps drinking) when they devised their DATE format. It's not a date, it's not a timestamp (because timestamps need to be a tad more precise than just the nearest second), it's just a nuisance.

In article <80m6p3$520$2_at_news8.svr.pol.co.uk>,   "Simon Hedges" <shedges_at_hhhh.freeserve.co.uk> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Nov 14 1999 - 08:14:31 CST