SQL Query to return records between 2 dates. 1

[ml23sh]

I'm working on this query where a user can search the database by name or by id or by dates. Everything works fine except when I try to search for records between 2 dates. It keeps telling me "a non-numeric is found where a numeric was expected". I've been staring at this for too long now so any help would be appreciated.

--This is an abridged version of the query.
where m_studies.study_date > To_Date(like '"+Request("field_H_ExamDt1&quot+"', 'MM/DD/YYYY')
AND m_studies.study_date < To_Date(like '&quot;+Request(&quot;field_H_ExamDt2&quot+&quot;', 'MM/DD/YYYY')

Thanks!

 

[Jerrycurl]

Why can't you just have
where m_studies.study_date > field_H_ExamDt1 and m_studies.study_date < field_H_ExamDt2
?
I'm not sure what's going on with &quot;like&quot; in the To_Date function (is To_Date a built-in function?) What database software are you using?

 

[carp]

The syntax and error message both look like Oracle, in which case your to_date function is what's killing you.
To_date expects two arguments: a character string and a format map. Consequently, since you've specified a format map of MM/DD/YYYY, that is exactly what your character string MUST look like. Like Jerrycurl, I don't know why you have
like'&quot;+Request(&quot;field_H_ExamDt1&quot+&quot; in the function call, but it SURE doesn't look like 'MM/DD/YYYY'!