Re: Search between date ranges

On Jul 30, 8:19 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Chris" <christopherc..._at_hotmail.com> schreef in berichtnews:73b00095-9ba4-4c9c-aa57-3a5e7636d893_at_56g2000hsm.googlegroups.com...
>
>
>
>
>
> > 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.
>
> > Instead I hav written :
>
> > AND ol.actual_shipment_date >= p_date_from
> > AND ol.actual_shipment_date <= (p_date_to)
>
> > If I have the p_date_from parameter set to 01-JAN-08 and the p_date_to
> > parameter set to 10-JAN-08 it will only return dates from 01-JAN to 09-
> > JAN (not the 10th)
>
> > To overcome this I have written :
>
> > AND ol.actual_shipment_date >= p_date_from
> > AND ol.actual_shipment_date <= (p_date_to +1)
>
> > This seems a very crude way of doing it and was wondering if there is
> > an easier way?
>
> > Thanks in advance,
> > Chris
>
> I guess you have to TRUNC your dates at both sides (trunc(date1) <=
> trunc(date2))
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -

As Shakespeare suggested you shoud trunc the target_date you are testing >= against and add one to the trunc end_target_date_range for the < (not <=) end. In the past the optimizer would automatically convert a BEWTEEN into >= and <= conditions (transformation). If the optimizer no longer does this then it is now probably unnecessary.

If you are looking only for rows affected on a single date I would use the >= and < syntax instead of BETWEEN otherwise you may need to set the ending time to 23:59:59 least you pick up entries with a time one second past what you want.

HTH -- Mark D Powell -- Received on Wed Jul 30 2008 - 07:52:08 CDT