Re: Search between date ranges
Date: Wed, 30 Jul 2008 08:18:46 -0700 (PDT)
On 30 Jul., 17:03, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "metzguar" <u..._at_ursmetzger.de> schreef in berichtnews:1f567507-b941-4ca9-bfd8-b204077cf2d0_at_27g2000hsf.googlegroups.com...
> > 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
> Only if p_date_to is a trunc-ed date (hh:mi = 00:00), but you're right.
> So to the OP: Yes, the crude way seems to be the best way ....
> BTW: I think a between would have required the +1 too...?
Yes, but you need to subtract a second from the to-date:
AND ol.actual_shipment_date BETWEEN trunc(p_date_from)
AND trunc(p_date_to) + 1 - 1/24/60/60
Urs Metzger Received on Wed Jul 30 2008 - 10:18:46 CDT