On Fri, 26 Jul 2002 13:37:47 -0400 "Kipp, James" <[EMAIL PROTECTED]> wrote:
>>> 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?
I don't think TRUNC() failed. It is more likely Oracle couldn't make sense
of the date strings in $fromDate and $toDate. The value of
$sth->{Statement} might make this more clear. I _REALLY_ don't like
depending on implicit conversions, especially for DATE values.
> Good idea, i ended up wrapping the WHERE clause in a $dbh->quote, and that
> worked fine.
I don't know how it could have worked correctly if you quote() the entire
WHERE clause. It should be used for individual data elements, not
expressions or clauses.
--
Mac :})
** I normally forward private questions to the appropriate mail list. **
Ask Smarter: http://www.tuxedo.org/~esr/faqs/smart-questions.html
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.
