CODEWITHSUNDEEP
coldfusion
Cmaso
Cmaso

Reputation: 1113

ColdFusion query of query date comparison

I have a query created from using cfspreadsheet that has a date column named "Actiondate", and looping through the query using isDate() shows that it's a date column, but in a query of that query it is being treated as a string. So when I apply a date filter in the where clause, it doesn't do the comparison correctly:

<cfquery ... >
SELECT *
FROM arguments.q
WHERE 1=1
<cfif isDate(arguments.dateFrom)>
    AND actiondate >= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#arguments.dateFrom#">
</cfif>
<cfif isDate(arguments.dateFrom)>
    AND actiondate <= <cfqueryparam cfsqltype="cf_sql_timestamp" value="#arguments.dateTo#">
</cfif>
</cfquery>

actiondate comes in the format of mm/dd/yyyy. When I do the where clause using string literals as a test, it works:

...where actiondate = '11/05/2015' --returns all rows with that "date"

<cfset tempdate = createdate(2015,11,5)>...
...where actiondate = <cfqueryparam cfsqltype="cf_sql_date" value="#temp#"> --returns nothing

But again, isDate(actiondate) returns true when looping through the query. I can work around the problem, but is there a way to do what I need to still using a query of query?

Upvotes: 1

Views: 2070

Answers (1)

Leigh
Leigh

Reputation: 28873

(From comments ...)

IsDate verifies a value can be converted into a date. That does not mean the value already is a date/time object. In the case of cfspreadsheet, the returned query values are strings. When you compare them to the cfqueryparam values, which are date/time objects, you are comparing apples and oranges. So the QoQ does an implicit conversion of the date/time values to string and comes up with the wrong answer.

To perform a date comparison on "ActionDate", you must use CAST to convert the strings into a date/time objects first. Assuming all of the values are valid date strings, in the format mm/dd/yyyy, try it with a hard coded value like 11/05/2015 first. Then plug in your variables.

WHERE CAST(actionDate AS DATE) = <cfqueryparam value="11/05/2015" 
                                     cfsqltype="cf_sql_date">

Upvotes: 4

Related Questions