Not getting desired output after executing a QoQ

Question

I'm running the following SQL queries and am not getting a desired output:

 <cfquery datasource = "XX.XX.X.XX" name="master">
    SELECT count(Timedetail) as Occurances, date_format(Timedetail,'%m-%d-%Y') 
    FROM   MyDatabase
    WHERE  EVENTS = "FIRST" GROUP BY Timedetail ;
 </cfquery> 
<cfquery dbtype="query" name="detail">
    SELECT  *
    FROM    master 
    WHERE  Timedetail >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
    AND    Timedetail <  <cfqueryparam value="#dateAdd('d', 1,form.enddate)#" cfsqltype="cf_sql_date">;
</cfquery>

The relevant database columns are:

TimeDetail: Holds all date and time related values
Events: Contains values like, FIRST, SECOND,THIRD etc. I have mentioned FIRST here for convenience and clarity.

As far as the startdate and enddate parameters are concerned, I have set them as follows:

 <cfparam name="form.startdate" default="#dateformat(now()-5, 'mm/dd/yyyy')#">
 <cfparam name="form.enddate" default="#dateformat(now()-1, 'mm/dd/yyyy')#">
 <cfparam name="form.selectdate" default="#dateformat(now(), 'mm/dd/yyyy')#">

So, my master query is displaying the following results:

  OCCURANCES  TIMEDETAIL
1   15712   06-06-2013
2   7533    06-07-2013
3   20899   06-10-2013
4   24075   06-11-2013
5   24219   06-12-2013
6   21485   06-13-2013
7   22661   06-14-2013
8   20010   06-15-2013
9   18032   06-16-2013
10  27588   06-17-2013
11  25861   06-18-2013
12  21106   06-19-2013
13  22281   06-20-2013
14  21736   06-21-2013
15  20060   06-22-2013
16  18384   06-23-2013
17  24233   06-24-2013
18  39901   06-25-2013
19  31132   06-26-2013
20  41744   06-27-2013
21  38926   06-28-2013
22  34910   06-29-2013
23  25682   06-30-2013
24  48400   07-01-2013
25  42847   07-02-2013
26  30014   07-03-2013
27  21047   07-04-2013
28  29982   07-05-2013
29  25056   07-06-2013
30  13733   07-07-2013
31  35753   07-08-2013
32  20966   07-09-2013
33  41713   07-10-2013
34  30976   07-11-2013

And, I'm wondering why my "detail" query is displaying nothing although I have specified startdate parameter as 2013-06-12 and enddate parameter as 2013-07-12 which is visible in the resultset as mentioned below. It should display the occurances and timedetail for the date range I have specified.

RESULTSET   
query
    OCCURANCES  TIMEDETAIL
CACHED  false
EXECUTIONTIME   0
SQL     SELECT * FROM master WHERE Timedetail >= ? AND Timedetail <?;
SQLPARAMETERS   
array
1   {ts '2013-06-12 00:00:00'}
2   {ts '2013-07-12 00:00:00'}

Leigh · Answer 1 · 2013-07-24T03:47:57.807

In memory queries (QoQ's) can be tricky when it comes to data types. They are far less savy about implicit data type conversions than a database would be. By using MySQL's date_format function, you are actually converting the datetime values into strings. So when you run your QoQ, CF may actually be performing a string comparison, which would yield very different results than a date comparison. Could explain why you are getting the wrong results.

Try changing your database query to return a datetime value instead of a string:

SELECT 
   COUNT(Timedetail) as Occurances
   , STR_TO_DATE( DATE_FORMAT(Timedetail,'%m-%d-%Y'), '%m-%d-%Y') AS Timedetail
FROM   ....
WHERE  ...

Update:

Another option is to CAST the value as a DATE in your QoQ. That would force the QoQ to perform a date comparison, instead of a string comparison:

WHERE  CAST(Timedetail AS DATE) >= <cfqueryparam value="#form.startdate#" 
                                          cfsqltype="cf_sql_date">

Thanks for your comment. I figured it out. Please read my comment above :) — Tan, Jul 16 '13 at 18:41

score 0 · Accepted Answer · edited Jul 31 '13 at 19:56

0

Well I figured it out on my own. The MySQL date_format() function returns a string, not a datetime object, so when running my query of query in ColdFusion I used the CF_SQL_VARCHAR datatype instead of CF_SQL_DATE and everything worked fine.

(Update from comments)

The answer above turned out to be wrong. When I used CF_SQL_VARCHAR I was getting unexpected results, in fact incomplete results. When I applied the STR_TO_DATE function, it worked fine.

edited Jul 31 '13 at 19:56

Leigh

28,765
10
55
103

answered Jul 16 '13 at 18:39

Tan

1,433
5
27
47

@Leigh Thanks a lot for your consistent help. Appreciated. – Tan Jul 16 '13 at 18:42
Oops, I just realized I misread your answer. If at all possible, it better to leave the column value as a `datetime` and perform a *date* comparison. Try the [work around I mentioned](http://stackoverflow.com/a/17684464/104223). String comparisons are a lot more finicky. Even an extra space might cause the query to return zero records. – Leigh Jul 16 '13 at 19:48
@Leigh You are absolutely right, I was getting unexpected results, in fact incomplete results. When I applied `STR_TO_DATE` function, it worked fine. Thanks again !! :) – Tan Jul 16 '13 at 22:46
BTW, given that the above does not work, it should not be marked as the answer. – Leigh Jul 24 '13 at 02:14
Lol, have you forgotten your comments from above? "Tan - @Leigh You are absolutely right, I was getting unexpected results, in fact incomplete results. When I applied STR_TO_DATE function, it worked fine. Thanks again !! :)" – Leigh Aug 01 '13 at 22:06
Lol, but eventually I ended up using DATE() function instead of STR_TO_DATE :) – Tan Aug 01 '13 at 22:20
That was later, and on a completely different thread. How would a stranger, viewing this thread a year, have any way of knowing that? I think you are missing the purpose of a community Q&A site ;-) Think of each question as a short story. A year from now, a stranger encounters the same problem. They read your question and the marked answer *thinking* it will solve their problem. Unfortunately, since you never updated your answer to indicate that what you posted did not really work, they would've used the wrong code ... – Leigh Aug 01 '13 at 22:46
.. and wasted more time trying to figure out why it did not work the way you said it did. Remember, this is site is a resource for others as well, not just you. So before you mark something as answered and move on - make sure it contains **accurate information**! That is the purpose of the edit feature - to improve answers as you learn more information. Otherwise you may end up causing someone else the same headaches you experienced. – Leigh Aug 01 '13 at 22:48
Hmm, got it. I'll remove it as a answer. – Tan Aug 01 '13 at 22:53

Not getting desired output after executing a QoQ

2 Answers2

Linked