mysql - cfquery 结果让我发疯

Question

我运行以下查询几个小时，进行了各种更改，但想知道为什么详细查询没有返回任何结果。我对其他连接也有类似的查询(FIRSTCONN、SECONDCONN、FOURTHCONN)，这些连接运行得非常好，并在输出中显示所需的日期范围。有人能找出原因吗？

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

附注作为测试，我在详细查询中运行“SELECT * from master”，并且获得了主查询和详细查询的相同输出，这是预期的，但是当我在详细查询中提到日期参数时，它停止显示输出。但是，其他连接查询(FIRSTCONN、SECONDCONN、FOURTHCONN 等)的情况并非如此

是否有其他方法可以运行此查询或我犯了什么错误？

转储第三大师

        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

转储第三个细节

THIRDCONN 第三次事件

Answer 1

听起来您的数据库中有一个 NULL 值，这导致您的查询失败。尝试用这样的东西运行查询

WHERE isNull(THIRDCONN,getDate())  >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_date"> 
AND isNull(THIRDCONN,getDate())<  <cfqueryparam value="#form.enddate#" cfsqltype="cf_sql_date">;

如果您有 NULL 值，则可以使用任何日期值代替 getDate()，那么这将确认该问题。