执行QoQ后未得到所需的输出 [英] Not getting desired output after executing a QoQ
问题描述
我正在执行以下SQL查询,但没有得到所需的输出:
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>
相关数据库列为:
-
TimeDetail
:保存所有与日期和时间相关的值 -
c $ c>:包含FIRST,SECOND,THIRD等值。为了方便和清晰,我在这里提到了FIRST。
TimeDetail
: Holds all date and time related valuesEvents
: 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
并且,我想知道为什么我的detail查询不显示任何内容,虽然我已经指定startdate参数为2013-06-12和enddate参数为2013-07-12这是在结果集中可见如下所述。它应该显示我指定的日期范围的事件和时间表。
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'}
推荐答案
来到数据类型。他们对隐式数据类型转换远不如数据库。通过使用MySQL的 date_format
函数,实际上是将datetime值转换为字符串。因此,当您运行您的QoQ时,CF可能实际上正在执行字符串比较,这将产生与日期比较非常不同的结果。可以解释为什么你得到错误的结果。
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 ...
$ b b
更新:
另一个选项是将值作为 DATE
在您的QoQ。这将迫使QoQ执行日期比较,而不是字符串比较:
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">
这篇关于执行QoQ后未得到所需的输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!