Skip to main content
A
Anonymous
November 16, 2012
Question

Query of queries and dates

  • November 16, 2012
  • 2 replies
  • 2457 views

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



DescriptiontheDate
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?

    This topic has been closed for replies.

    2 replies

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    November 17, 2012

    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]

    A
    Adam Cameron.
    Inspiring
    November 17, 2012

    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
    Community Expert
    BKBKCommunity Expert
    Community Expert
    November 17, 2012

    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.

    A
    Adam Cameron.
    Inspiring
    November 16, 2012

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

    --

    Adam

    D
    Dan_Bracuk
    Inspiring
    November 17, 2012

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

    A
    Adam Cameron.
    Inspiring
    November 17, 2012

    "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

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices