Skip to main content
N
newone8888
Participant
July 12, 2013
Answered

Need help with query between 2 dates

  • July 12, 2013
  • 1 reply
  • 1250 views

Hi,

        I have not used CF in long time and need help with a simple query.

I have an access DB table with 3 fields, name, date and number

What I want to is to create a query to pull all names between 2 dates

When I query the date field, the results are showing in this formate 2013-07-12 00:00:00

Here is my query

<cfquery datasource="mydb" name="test">

Select name from mytable

where edate Between '2011-01-01 00:00:00' AND '2013-01-01 00:00:00'

</cfquery>

<cfoutput query="test">

#name#

</cfoutput>

What I get is this error

ODBC Error Code = 22005 (Error in assignment)

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

Not sure what I'm doing wrong here.

Please let me know.

Thanks

    This topic has been closed for replies.
    Correct answer p_sim

    Thanks. I'm a bit over my head here.

    Can you give me an example of how to use the cfparamter in my query above?


    <cfset startDate = createODBCDate("2011-01-01") />

    <cfset endDate = createODBCDate("2013-01-01") />

    <cfquery datasource="mydb" name="test">

              SELECT ename

              FROM mytable

              WHERE edate BETWEEN

                                  <cfqueryparam value="#startDate#" cfsqltype="cf_sql_timestamp" />

                   AND

                                  <cfqueryparam value="#endDate#" cfsqltype="cf_sql_timestamp" />

    </cfquery>

    <cfoutput query="test">

    #ename#

    </cfoutput>

    1 reply

    A
    Adam Cameron.
    Inspiring
    July 12, 2013

    You might wanna pass those dates as *dates* rather than strings. Use <cfqueryparam>. However I would have thought even Access could automatically cast that format of string to a date.

    Other than that, are you sure "name" is not a reserved word? I dunno one way or the other, but it's the sort of thing I could imagine being a reserved word.

    Finally: [insert obligatory derision about someone using a desktop DB in a server-side situation]

    --

    Adam

    N
    newone8888Author
    Participant
    July 12, 2013

    Thanks. sorry, name should be ename.

    Not sure how to pass the dates as dates, can you give me an example?

    p_sim
    p_sim
    Participating Frequently
    July 12, 2013

    The example is in the CF docs.

    http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_18.html

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices