Copy link to clipboard
Copied
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?
Copy link to clipboard
Copied
Well "now()" is just the literal string "now()". You mean "#now()#". It's a CF function, not a QoQ/IMQ function.
--
Adam
Copy link to clipboard
Copied
I know I can look it up but others might be interested in the answer. IMQ funcion???
Copy link to clipboard
Copied
"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
Copy link to clipboard
Copied
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]
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.