> > ---
> > This does not work because the trunc is on the param data
> coming from the
> > cgi, not a big deal, so we trunc the actual oracle field instead:
> > SELECT * FROM stats
> > WHERE TRUNC(sdate) BETWEEN '$fromDate' AND '$toDate';
>
> There is a performance penalty when using any function with
> the column in a
> WHERE clause. That is why the '+1' trick is useful for DATE
> comparisons.
Ok, but do you know why trunc fails here?
>
> > Here is the what worked:
> > SELECT * FROM stats
> > WHERE sdate BETWEEN TO_DATE('$toDate','MM/DD/YYYY')
> > AND TO_DATE('$fromDate','MM/DD/YYYY');
> >
> > and WE DO NEED theh +1 to get the $toDate included
>
> You should use placeholders instead of pasting the date strings into
> the SQL. That avoids all the confusion about the quotes around the
> arguments and is normally more efficient, especially if the same query
> is sent to the database more than once (even from other
> processes). This
> looks like a query that your database will be seeing a lot.
> Oracle can
> avoid replanning it if you use placeholders.
Good idea, i ended up wrapping the WHERE clause in a $dbh->quote, and that
worked fine.
