You need to convert the FORM.book_checkin_date and other date
to DATE format. Currently, it is define as CHAR not DATE. In
Oracle, to_date function format string as date, to_char format date
as string. I am not sure how to do it SQL Server but google should
be your answer.
To follow up on john85's answer, cold fusion has a number of
functions that will help you. They have names like createdate,
createodbcdate, etc. Since the value is coming from a form, it's a
good idea to verify that the value actually represents a valid
SQL can read and convert character-formatted dates. I suspect
you have a problem with the order of date elements. What collation
is SQL Server using? Do you submit dates in MM/DD/YYYY format, or
The safe approach is to use the ODBCDate functions.
use <cfqueryparam cfsqltype="cf_sql_date"
value="#form.book_checkout_date#">; in fact, it's best practice
to use cfqueryparam for all of your query parameters; also, if
you're date is actually a timestamp data type in the database,
cfsqltype="cf_sql_timestamp" should be used instead.
I agree with Hess that you should use cfqueryparam. The
cfsqltype attribute should be "cf_sql_timestamp" for DATETIME
fields in Microsoft SQL Server.
SQL will implicitly convert strings that contain dates to
datetime values. I'd verify that your form variables contain valid
dates. What data type are your date fields and what values do your
form variables contain?