Skip navigation
Currently Being Moderated

Query of queries and dates

Nov 16, 2012 2:01 PM

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?

 
Replies
  • Currently Being Moderated
    Nov 16, 2012 2:06 PM   in reply to lkellyv

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

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 16, 2012 4:07 PM   in reply to Adam Cameron.

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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 17, 2012 12:16 AM   in reply to Dan Bracuk

    "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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 17, 2012 1:10 AM   in reply to lkellyv

    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]

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 17, 2012 2:23 AM   in reply to BKBK

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 17, 2012 2:45 AM   in reply to Adam Cameron.

    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.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points