CODEWITHSUNDEEP
mysqlcoldfusioncoldfusion-9qoq
Saurabh Sharma
Saurabh Sharma

Reputation: 317

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

Upvotes: 2

Views: 574

Answers (3)

Saurabh Sharma
Saurabh Sharma

Reputation: 317

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')#">

Upvotes: 2

Dan Bracuk
Dan Bracuk

Reputation: 20804

The problem is that time without a date is meaningless. Therefore time datatypes have an invisible date applied to it. However, different softwares apply different dates to time datatypes.

ColdFusion applies appears to apply 1899-12-30. This code:

<cfdump var="#CreateTime(18,0,0)#">

returns {ts '1899-12-30 18:00:00'}

However, when I run a database query:

<cfquery name="x" datasource="dw">
 select registration_number, event_time
 from admit_fact

 where date = yesterday
 and discharge
 and datepart(hour, event_time) < 13
 </cfquery>
 <cfdump var="#x#" metainfo="no">

I see 19 rows with values like {ts '1970-01-01 11:30:00'}. As a result, a QofQ like this:

<cfquery name="y" dbtype="query">
 select *
 from x
 where event_time < <cfqueryparam cfsqltype="cf_sql_time" value="#CreateTime(18,0,0)#"> 
 </cfquery>

will not return any rows. In the "it's worth a shot" category, even I tried:

where cast(event_time as time) < 
<cfqueryparam cfsqltype="cf_sql_time" value="#CreateTime(18,0,0)#">

but the results did not change. Like Saurabh, I'd also like to see a simpler way to compare the values.

Upvotes: 0

swetha
swetha

Reputation: 130

you need to convert date time format with createodbcdatetime()/CreateODBCTime() function to convert current date and time to compare with DB date and time format column

Upvotes: 0

Related Questions