SELECT query Error, possible date problem?

[rowbeast]

Hello, I have what I thought was a fairly straight forward date based query
that is returning an error I cant seem to get past. Here is my scenario: I was
handed this code what was supposedly functional on a CF 4.5 box running SQL
server 7. Due to a hosting provider change, the platform was upgraded to CF7
and SQL2k sp3. Now the code doesnt work. There are 2 formfields being passed
that contain valid dates in mm/yy/dddd format to the following query. the date
adds are so that I can search 3 days before and after the submitted form field
dates. <cfset today_int=#CreateODBCDate(Now())#> <cfset s_date_low =
#dateAdd('d', -3, form.start_dt)#> <cfset e_date_high = #dateAdd('d', 3,
form.end_dt)#> <cfquery name='get_getaways' datasource='#application.dsn#'>
SELECT * FROM tblgetaway2 WHERE start_dt > #s_date_low# AND end_dt <
#e_date_high# AND active=1 AND booked=0 AND (hotwk=0 OR hotwk=2) AND
start_dt>#today_int# ORDER BY start_dt </cfquery> This Query always results in
Error Executing Database Query. [Macromedia][SQLServer JDBC
Driver][SQLServer]Syntax error converting datetime from character string. My
database field types are set as datetime. Can anyone help? I'm at a loss at
this point...

[jdeline]

Depending on how your dates are defined in your database, you may need to treat them as strings. For example,
WHERE start_dt > '#s_date_low# ' ...

[tanging]

try the <CFQUERYPARAM CFSQLTYPE='CF_SQL_DATE' VALUE='##'> It will format the
date for the appropriate datasource you are using (thus you needn't worry if it
is DB2's 'yyyy-mm-dd' or Oracle's 'dd-mmm-yy' etc). If this fails and you have
the ability, verify that the database is indeed using DATES in these fields (if
not, you can have issues with > or < on text fields that are holding dates -- I
have seen issues on this esp in MS Access); get them changed to dates or
decimals or something that does not do a dictionary attack for gt and lt...
<cfquery name='get_getaways' datasource='#application.dsn#'> SELECT * FROM
tblgetaway2 WHERE start_dt > <CFQUERYPARAM CFSQLTYPE='CF_SQL_DATE'
VALUE='#s_date_low#'> AND end_dt < <CFQUERYPARAM CFSQLTYPE='CF_SQL_DATE'
VALUE='#e_date_high#'> AND active=1 AND booked=0 AND (hotwk=0 OR hotwk=2) AND
start_dt> <CFQUERYPARAM CFSQLTYPE='CF_SQL_DATE' VALUE='#today_int#'> ORDER BY
start_dt </cfquery>