Comparing Access Date/Time to Coldfusion date

[drtyret]

Hello,

I have written a query to retrieve some data from my database. However, it is conditional upon my WHERE clause. The SQL query iterates over the database records and makes a comparison. This WHERE clause comparison consists of comparing a date/time formatted object in a field in the Access database to a date entered by user via a form.

The problem i am having is that i cannot format the variable submitted via the form so that it is in the same format as the date/time entry in the databases field. I need to convert the string variables submitted by the user via the form to an ODBCDATETIME i imagine using a CF function, but this didn't work (unless i was doing something wrong).

My question is: how can i format day/month/year (e.g. 01 01 2006) form variables so that they are the in a format that can be used to compare them to an Access date/time field variable (e.g. .1982-07-27 00:00:00.0). N.B. All times in my Access date/time field are equal to 00:00:00.0

Also note that i cannot simply compare a string to an Access date/time because it will cause an execution problem within the query.

Neil

[paross1]

Something like this?

SELECT.....
WHERE access_date_field = #CreateODBCDate(DateFormat("01/01/2006", "dd/mm/yyyy"))#

--or--

SELECT.....
WHERE access_date_field = #CreateODBCDateTime(DateFormat("01/01/2006", "dd/mm/yyyy"))#

The "01/01/2006" can be replaced by a variable that contains the date string, assuming that it matches the date mask in the DateFormat() function.

Phil

[Newsgroup_User]

WHERE aDateField =

<cfqueryparam value="#CreateODBCDate(aDateString)#" cfsqltype="cf_sql_date">

OR

<cfqueryparam value="#CreateODBCDateTime(aDateTimeString)#"
cfsqltype="cf_sql_timestamp">

OR if you want more control over how the date string is parsed.

<cfqueryparam value="#CreateODBCDate(parseDate(aDateString)#"
cfsqltype="cf_sql_date">

[Newsgroup_User]

parseDateTime() or LSparseDateTime() - not parseDate()

I always do that.