• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Query of queries and dates

Guest
Nov 16, 2012 Nov 16, 2012

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:

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?

Views

2.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 16, 2012 Nov 16, 2012

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 16, 2012 Nov 16, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 17, 2012 Nov 17, 2012

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 17, 2012 Nov 17, 2012

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]

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Nov 17, 2012 Nov 17, 2012

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 17, 2012 Nov 17, 2012

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation