I had some strange behavior occur sorting a query of a query. The query I was querying and sorting was one that I created manually through code, it was not a query off a database to begin with.

I was attempting to sort on a date field. Which was in a SQL server datetime format. {ts '2004-03-22 15:11:29'}, but for some reason sorting on this field did not always yield a proper result.

I modified the code where the date was being assigned to the original query with querySetCell() and used createODBCDateTime() function to convert the MS Sql server date before putting it into my created query.

After doing so the query of the query and sorting on the date worked properly.

<cfscript>
tmpQry = queryNew("title,comment_date,data");
queryAddRow(tmpQry, 5);
querySetCell(tmpQry, "title", "Last Comment:", 1);
querySetCell(tmpQry, "title", "Last Response:", 2);
querySetCell(tmpQry, "title", "Last Taken By:", 3);
querySetCell(tmpQry, "title", "Last Freed By:", 4);
querySetCell(tmpQry, "title", "Last Owner Change:", 5);
if(isDate(tickets.max_comment_date)){
querySetCell(tmpQry, "comment_date", createOdbcDateTime(tickets.max_comment_date), 1);}
if(isDate(tickets.max_correspondence_date)){
querySetCell(tmpQry, "comment_date", createOdbcDateTime(tickets.max_correspondence_date), 2);}
if(isDate(tickets.max_taken_date)){
querySetCell(tmpQry, "comment_date", createOdbcDateTime(tickets.max_taken_date), 3);}
if(isDate(tickets.max_freed_date)){
querySetCell(tmpQry, "comment_date", createOdbcDateTime(tickets.max_freed_date), 4);}
if(isDate(tickets.max_ownerchange_date)){
querySetCell(tmpQry, "comment_date", createOdbcDateTime(tickets.max_ownerchange_date), 5);}
querySetCell(tmpQry, "data", tickets.max_comment, 1);
querySetCell(tmpQry, "data", tickets.max_correspondence, 2);
querySetCell(tmpQry, "data", tickets.max_taken, 3);
querySetCell(tmpQry, "data", tickets.max_freed, 4);
querySetCell(tmpQry, "data", tickets.max_ownerchange, 5);
</cfscript>
<cfquery dbtype="query" name="fqry">
SELECT title, data, comment_date
FROM tmpQry
ORDER BY comment_date DESC
</cfquery>

I've also run into this problem, it occurs both when creating record sets on the fly, and when running query-of-queries procedures. By default, CF casts the data types as strings. If you don't create the dates using a CF function, it treats them as string data. When you run IsDate() it simply checks to see if they match date formats for date functions, it doens't tell you if the data is flagged as a date by CF. When creating record set structures recast the string data as a date by using CreateDate() or some such function. When using a date field in the WHERE clause of a query-of-queries, use the <CFQUERYPARAM> tag to cast the input as a date, so CF knows how to compare the columns.