Re: Search between date ranges
Date: Wed, 30 Jul 2008 17:03:48 +0200
"metzguar" <urs_at_ursmetzger.de> schreef in bericht
> 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
> 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
> 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,
>> 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...?
Shakespeare Received on Wed Jul 30 2008 - 10:03:48 CDT