#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    3
    Rep Power
    0

    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
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    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>
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    3
    Rep Power
    0
    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.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    3
    Rep Power
    0
    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>
  8. #5
  9. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,273
    Rep Power
    968
    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.

IMN logo majestic logo threadwatch logo seochat tools logo