Search code examples
mysqlcoldfusioncoldfusion-8cfml

Following cfquery result is driving me crazy

I'm running the following query for many hours, made all sorts of changes but wondering why the DETAIL Query is not returning any results. I have similar queries with me for other connections as well( FIRSTCONN, SECONDCONN, FOURTHCONN) which are running absolutely fine and displaying desired range of dates in the output. Could anyone figure out what could be the reason?

<cfquery datasource = "XX.XX.X.XX" name="master">
SELECT STR_TO_DATE(date_format(Timedetail,'%m-%d-%Y'),'%m-%d-%Y') as THIRDCONN,count(Timedetail) as THIRDOCCURANCES ,EVENTS 
FROM MyDatabase
WHERE EVENTS = "THIRD" 
GROUP BY THIRDCONN;
 </cfquery> 


  DUMP FOR THIRD MASTER <cfdump var = "#master#"> 





<cfquery dbtype="query" name="detail">
SELECT  *
FROM master 
WHERE THIRDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
AND THIRDCONN  <  <cfqueryparam value="#form.enddate#" cfsqltype="cf_sql_date">;
</cfquery>  


   DUMP FOR THIRD DETAIL <cfdump var = "#detail#">

P.S. Just as a test, I ran "SELECT * from master" in the detail query and I got the identical output for master and detail queries which was expected but when I mention the dates parameters in the detail query, it stops displaying the output.However, this is not the case with other connection queries (FIRSTCONN, SECONDCONN, FOURTHCONN etc)

Is there any other way I can run this query or any mistake I have done?

DUMP FOR THIRD MASTER

        THIRDCONN   THIRDOCCURANCES     EVENTS
   1   {ts '2013-06-06 00:00:00'}   10810   THIRD
2   {ts '2013-06-07 00:00:00'}  8076    THIRD
3   {ts '2013-06-10 00:00:00'}  25043   THIRD
4   {ts '2013-06-11 00:00:00'}  24754   THIRD
5   {ts '2013-06-12 00:00:00'}  14587   THIRD
6   {ts '2013-06-13 00:00:00'}  24828   THIRD
7   {ts '2013-06-14 00:00:00'}  23987   THIRD
8   {ts '2013-06-15 00:00:00'}  28027   THIRD
9   {ts '2013-06-16 00:00:00'}  25190   THIRD
10  {ts '2013-06-17 00:00:00'}  27255   THIRD
11  {ts '2013-06-18 00:00:00'}  22227   THIRD
12  {ts '2013-06-19 00:00:00'}  15951   THIRD
13  {ts '2013-06-20 00:00:00'}  21120   THIRD
14  {ts '2013-06-21 00:00:00'}  24214   THIRD
15  {ts '2013-06-22 00:00:00'}  22466   THIRD
16  {ts '2013-06-23 00:00:00'}  19881   THIRD
17  {ts '2013-06-24 00:00:00'}  23479   THIRD
18  {ts '2013-06-25 00:00:00'}  7879    THIRD

DUMP FOR THIRD DETAIL

THIRDCONN THIRDOCCURANCES EVENTS

Solution

  • Well, I figured out on my own. There is no need of STR_TO_DATE function in SQL Query here.I just used DATE() function to retrieve the date part. Also, I noticed that specifying the mask while using DateFormat function as yyyy-mm-dd was helpful for coldfusion rather than specifying any other format.

    Thanks to all for your help. Appreciated.

    Working Code:

    <cfparam name="form.startdate" default="#DateFormat(dateAdd('d',-40,now()), 'yyyy-mm-dd')#">
<cfparam name="form.enddate" default="#DateFormat(dateAdd('d',-1,now()), 'yyyy-mm-dd')#">


<cfquery datasource = "XX.XX.X.XX" name="master">
SELECT DATE(Timedetail) as THIRDCONN,count(Timedetail) as THIRDOCCURANCES ,EVENTS 
FROM MyDatabase
WHERE EVENTS = "THIRD" 
GROUP BY THIRDCONN;
 </cfquery> 





<cfquery dbtype="query" name="detail">
SELECT  *
FROM master 
WHERE THIRDCONN  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
AND THIRDCONN  <  <cfqueryparam value="#form.enddate#" cfsqltype="cf_sql_date">;
</cfquery>