Re: Search between date ranges

On 30 Jul., 14:47, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote: Comments embedded.
> On Jul 30, 5:23 am, Chris <christopherc..._at_hotmail.com> wrote:
>
> > Hi Guys,
>
> > I am trying to bild a cursor based on a select statement which returns
> > values between a certain date range. I have read that it is best
> > avoiding the BETWEEN function of SQL.
>
> Where did you read that? It clearly is not true:
...
> Note both queries produce the same execution plan, with the same
> filter, as BETWEEN is silently rewritten to the form you've
> implemented.

full ack

> A solution has already been presented, but I will repeat it here:
>
> AND trunc(ol.actual_shipment_date) >= p_date_from
> AND trunc(ol.actual_shipment_date) <= p_date_to

Bad! This way the database can't use an index on ol.actual_shipment_date.

Instead, use

AND ol.actual_shipment_date >= p_date_from AND ol.actual_shipment_date < p_date_to + 1

this will include records from p_date_from, 0:00:00 through p_date_to, 23:59:59

> Of course it appears you're relying upon a default date mask,

I can't see why. He is building a cursor, so maybe he uses PL/SQL, and p_date_from and p_date_to might well be true DATEs.

Urs Metzger Received on Wed Jul 30 2008 - 09:51:48 CDT