Query of queries and dates

[Guest]

I converted some XML into a query (just two columns - a date and description).  If I cfdump my query, I get the following:

Description	theDate
abc	{ts '2012-11-05 00:00:00'}
cde	{ts '2012-11-06 00:00:00'}
efg	{ts '2012-12-04 00:00:00'}
ghi	{ts '2012-12-01 00:00:00'}

What I want to do now is just a simple query to get the records with a date >= today's date.

<cfquery name="datetest" dbType="query">

SELECT *

FROM myquery

WHERE theDate >= now()

</cfquery>

This of course does not work.  When I created the original query represented by the table above, I did:

<cfset myquery = QueryNew("theDate, description", "date, varchar")>

I'm hoping this is just a simple syntax issue, but I really need to be able to do date comparison in my query of a query.  Any help?

[Adam Cameron.]

Well "now()" is just the literal string "now()". You mean "#now()#".  It's a CF function, not a QoQ/IMQ function.

--

Adam

[Dan_Bracuk]

I know I can look it up but others might be interested in the answer.  IMQ funcion???

[Adam Cameron.]

"in-memory query", I think. It's how the QoQ engine is referred to in the underlying ColdFusion code.

QOQ is what the code does, it's implemented with something ColdFusion refers to as IMQ (in packages and class names, etc).

--

Adam

[BKBK]

What database engine are you using? They speak slightly different SQL dialects.

Your query is valid in MySQL, where the now() function represents the current datetime. It is just coincidence that ColdFusion, too, has this function. In MS SQL, getDate() returns the current date.

[added edit: removed superfluous remark]

[Adam Cameron.]

Adds per the subject line and the chide itself, it's a QoQ. So the only syntax support that matters is what QoQ supports.

--

Adam

[BKBK]

Adam Cameron. wrote:

Adds per the subject line and the chide itself, it's a QoQ. So the only syntax support that matters is what QoQ supports.

Sorry, ignore me. I got blindsided by the fact that the clause WHERE theDate >= now() did return results! Still weird.