can QofQ do date comparisons?

[3dColor]

This does not work:

<cfquery name="GetStats" datasource="#DSN#" >
   	SELECT 		*
   	FROM 		Stats
	WHERE 		ListingFk = #URL.ad#
</cfquery>

<CFQUERY name="QofQtoday" dbtype="query">
   	SELECT 		*
   	FROM 		GetStats
	WHERE		DATE(timeStampStats) = CURRENT_DATE
</CFQUERY>

But this one does:

<CFQUERY name="today" datasource="#DSN#">
   	SELECT 		*
   	FROM 		Stats
	WHERE		DATE(timeStampStats) = CURRENT_DATE
</CFQUERY>

I get the following error for the top query:

Query Of Queries syntax error.
Encountered "DATE. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition

Can QofQ do date comparisons?

 

[TonyGroves]

You probably have a good reason for doing it in two queries, but if not, then surely it be simpler to do it in one?
[tt]
SELECT *
FROM stats
WHERE
listingfk=#URL.ad#
AND DATE(timestampstats)=CURRENT_DATE
[/tt]

 

[3dColor]

The original query retrieves the total stats, and the QofQ retrieves the stats just for today. I would rather not have to query the DB twice.

So, can QofQ do date comparisons?

 

[TonyGroves]

I've no idea what QofQ is, but in this query it's MySQL that will do the date comparisons and return the appropriate data.

 

[r937]

tony, QofQ (query of query) is a ColdFusion feature

3d, yes, QofQ can do date comparisons, but you have to use QofQ syntax, because at that point it is no longer a mysql query, it is a query running in ColdFusion against the ColdFusion query results structure

<CFQUERY name="QofQtoday" dbtype="query">
select * from GetStats
where #DateFormat(timeStampStats,"yyyymmdd")#
= #DateFormat(Now(),"yyyymmdd")#
</CFQUERY>


r937.com | rudy.ca