2

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

Leigh
  • 28,765
  • 10
  • 55
  • 103
Saurabh Sharma
  • 317
  • 2
  • 11

3 Answers3

2

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

Saurabh Sharma
  • 317
  • 2
  • 11
0

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

swetha
  • 132
  • 5
  • I tried below code but still bad luck. ` SELECT count(*) as mycount FROM getExams WHERE start_time <= ` – Saurabh Sharma Apr 25 '13 at 06:23
  • try using this SELECT count(*) as mycount FROM getExams WHERE start_time <= #createodbcdatetime(curr_time)#" – swetha Apr 25 '13 at 06:24
  • Still it's not working with even createODBCDateTime. I know there's a solution provided online at [asadesigner.com](http://www.asadesigner.com/16-coldfusion/b033bba6f200c1c5.htm) and I have tried using timestamp also. Still no luck. :( – Saurabh Sharma Apr 25 '13 at 06:32
  • your column contains both date time with or only date – swetha Apr 25 '13 at 06:34
  • try using this time format it creates 24hr time format of current time #TimeFormat(now(), "HH:mm:ss")# – swetha Apr 25 '13 at 06:42
  • 1
    Many thanks @swetha for helping to solve my problem. Well i have found an alternative to get the expected results. I had to use `CONVERT(DATE_FORMAT(db_time_col, '%H%i'), UNSIGNED INTEGER) as db_time_col` in MySQL query and then used `` to compare time columns in QOQ. I wish there was a simpler way to compare these values. – Saurabh Sharma Apr 25 '13 at 07:15
  • @Saurabh - There is clearly a problem with `time` values, so consider submitting a [bug report](https://bugbase.adobe.com). Also, you should post your work-around as an "answer" so it is easier for others to find in the future. – Leigh Apr 25 '13 at 19:28
0

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.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • If you can determine what your database server is using as the "hidden" date (1970-01-01 in your example), wouldn't it be easier to just use the `CreateODBCDateTime()` function using the default date of your database server and then the desired time? – Miguel-F Apr 25 '13 at 13:14
  • We can do anything you want once you know what we are dealing with. However your suggestion while just as effective as what @Saurabh eventually did, it's not necessarily simpler. – Dan Bracuk Apr 25 '13 at 14:00