3 Replies Latest reply on Oct 4, 2006 11:11 AM by Newsgroup_User

    Comparing Access Date/Time to Coldfusion date


      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.