Re: Search between date ranges

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