Date from string

Here is the code I am running, first the DateSubmitted is a text file in the database (can't change the database) I want to convert it to date format while comparing against today's date. I am at a rock wall trying to get it to work. Any help would be great, thanks.


<CFQUERY NAME="GetResultsATL" DATASOURCE="ATLDBConnector">
SELECT prDateSubmitted, COUNT_PAGES_RECEIVED, COUNT_PAGES_SENT
FROM DBConnTable
WHERE #DateFormat(Now())# = #ParseDateTime(prDateSubmitted)#
</CFQUERY>

An error occurred while evaluating the expression:
#ParseDateTime(prDateSubmitted)#
Error near line 7, column 30.
Error resolving parameter PRDATESUBMITTED
ColdFusion was unable to determine the value of the parameter. This problem is very likely due to the fact that either:
You have misspelled the parameter name, or
You have not specified a QUERY attribute for a CFOUTPUT, CFMAIL, or CFTABLE tag.
The error occurred while processing an element with a general identifier of (#ParseDateTime(prDateSubmitted)#), occupying document position (7:29) to (7:60

Your mistake is that you are trying to use a CF function on a database field. While CF is processing this cfquery block, it has no idea what prDateSubmitted is. By the time the query is handed to the database for processing, CF is done with it. Assuming that prDateSubmitted is already in a native ODBC date/time format in the database, try something like this:


<CFQUERY NAME="GetResultsATL" DATASOURCE="ATLDBConnector">
SELECT prDateSubmitted, COUNT_PAGES_RECEIVED, COUNT_PAGES_SENT
FROM DBConnTable
WHERE prDateSubmitted = #createODBCDate( Now() )#
</CFQUERY>

That is the problem the datesubmitted is not a date/time field it is a text field. I am trying to convert it while comparing the two.

I am trying to use ParseDateTime to convert a text string as a date in the database to a ODBCDate to use in a query. Please Help...!!

I change the code a little.

<CFQUERY NAME="GetResultsATL" DATASOURCE="ATLDBConnector">
SELECT prDateSubmitted, COUNT_PAGES_RECEIVED, COUNT_PAGES_SENT
FROM DBConnTable
WHERE ParseDateTime(prDateSubmitted) = #createODBCDate(Now()-2)#
</CFQUERY>

You CAN'T apply a ColdFusion function to a database field like that. CF has no idea what prDateSubmitted is because while CF is processing this code nothing has been sent to the database yet. You will need to use the database's native functions to format the field.