QoQ date filter

[gr8white]

I thought this would be straightforward but apparently not. I have a query
that selects a date field from an Oracle table. I then want a query of queries
to return those rows where the date is between two values represented by form
parameters.

I have searched in vain for the answer to this. I thought
...where date_field between #CreateODBCDate(begindate)# and
#CreateODBCDate(enddate)#
should work but that causes a Query Of Queries syntax error (the SQL shown
with the error confirms the ODBC date is created correctly).

Some references suggest I should use CFQUERYPARAM but I haven't had any luck
with my attempts to do so.

Any suggestions?

Ken

[paross1]

I havent tried this in a Q-of-Q, but you might try using the ParseDateTime()
function to create your date/time object, rather than CreateODBCDate. Something
like this?

....where date_field between #ParseDateTime(begindate)# and
#ParseDateTime(enddate)#

Phil

[gr8white]

Phil, thanks for the suggestion.

I did finally get this to work using CFQUERYPARAM (and also correcting a
syntax error that was getting in the way). For the benefit of anyone else who
might run into this issue, the solution is below.
Ken


where scan_date between <cfqueryparam cfsqltype="cf_sql_date"
value="#begindate#">
and <cfqueryparam cfsqltype="cf_sql_date" value="#enddate#">