Coldfusion Query of Queries and dateformat error

Question

Documents indicate that I should be able to use the DateFormat() function to format inside of a QoQ, But I am getting an error - I have tried cast() in addition to normal... I would like to return a query with date parts broken out (month and year) or filter based on datepart to pull back only record that match a month and year

So my objective would look like this in a normal Query.

SELECT
name
,date AS fulldate
,month(date) AS month
,year(date) as year

OR

WHERE
month(date) = arguments.month
and year(date) = arguments.year

However when I try to effectively do this in QoQ - I get a weird error.

Here is what I've tried:

SELECT 
directory + '\' + name AS fullpath ,
size ,
DateLastModified ,
dateformat(DateLastModified) AS month 
FROM getAllFiles 
ORDER BY DateLastModified

Here is the error

Query Of Queries syntax error.
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but encountered '(' instead, A select statement should have a 'FROM' construct.

I have also tried this (and got the same error)

SELECT 
directory + '\' + name AS fullpath ,
size ,
DateLastModified ,
cast( dateformat(DateLastModified,'mm-dd-yyyy') as date ) AS month 
FROM getAllFiles ORDER BY DateLastModified

Help greatly appreciated Cheers

SOS · Answer 1 · 2022-02-24T01:16:38.353

Documents indicate that I should be able to use the DateFormat() function to format inside of a QoQ,

Unfortunately, no. If you're referring to this note in the documentation, it's just poorly worded. I suspect they meant to say, "use [that] function outside the QoQ. For example, when outputting results to the browser withing a query loop".

If you want to convert the date to its original format, use the DateFormat_ function and apply the "mm/dd/yy" mask._

QoQ's are extremely limited and currently only support these functions:

Conversion functions: CAST()
String functions: UPPER() and LOWER()
Aggregate functions: AVG(), COUNT(), MAX(), MIN()

Could you elaborate on why you're trying to format the string column DateLastModified so we can suggest an alternative?

Coldfusion Query of Queries and dateformat error

1 Answers1