Re: Search between date ranges

"metzguar" <urs_at_ursmetzger.de> schreef in bericht news: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...?

Shakespeare Received on Wed Jul 30 2008 - 10:03:48 CDT