Search code examples
mysqlcoldfusioncoldfusion-9qoq

ColdFusion QOQ, compare time values?

I'm trying to execute the below code using ColdFusion QOQ but it's not retrieving any records. I am using time in format "HH:mm" and using MySQL as backend and the original time column has a datatype of "TIME".

<cfquery dbtype="query" name="getCount">
    SELECT count(*) as mycount
    FROM getExams
    WHERE start_time <= <cfqueryparam cfsqltype="cf_sql_time" value="#curr_time#">
</cfquery>

I am able to successfully compare date values using <cfqueryparam>. However, it's not working on time columns. Can anyone help?

Update:
There is an open bug report for this issue. See bug #3551866

Solution

  • Even after many hours of effort, I was not able to find a simple way to compare time values within ColdFusion QOQ. So, here's the workaround that I used:

    Step 1
    To retrieve db columns with Time datatype, use format hhmmss.
    eg. in my case of MySQL I used:
    CONVERT(DATE_FORMAT(db_time_col, '%H%i'), UNSIGNED INTEGER) as db_time_col

    Step 2
    Within ColdFusion QOQ, convert time value into same format as used in Step 1.
    eg. in my case it is: <cfqueryparam cfsqltype="cf_sql_integer" value="#timeFormat(cf_time_col, 'HHmm')#">