Re: Search between date ranges
Date: Wed, 30 Jul 2008 07:51:48 -0700 (PDT)
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
> 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.
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