Hi Peter, You could include year, month and day as separate integer columns to sort on and that would work (though you'd have to get those values from the database). It might be easier to draw a single extra column from the database using CAST to convert the date value to an integer. This produces what's called a Julian date. You can then easily sort the results of a query of query drawn from the original db query using the julian date and get back the results you're hoping for.
More information about julian and other mathematical time systems at http://www.decimaltime.org hth > Hello! > I am trying to order a dynamically created query (query of > query) by a > date > column. That does not work. Now I've tried the following > line in order to > try to sort by a string: > <cfset QuerySetCell(query1 "dt_new", DateFormat > (query_original.date_field, "yyyymmdd")&TimeFormat > (query_original.date_field, "HHmm"), query1 .currentrow)> > <cfdump> shows me that the values are set correctly but a > <cfquery dbtype="query" name="query_new"> > SELECT * FROM query1 > ORDER BY dt_new; > </cfquery> > still orders the values in a wrong order. It seems that > only the first > chars are checked for sorting. > Any workarounds? I am thinking about adding several fields > like "date_year", "date_month", ... and then performing an > ORDER BY > date_year,date_month, ... > Software: MX; Updater 3 applied. > Best regards, > Peter > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ~~~~~~~~~~~| > Archives: > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index. > cfm?method=subscribe&forumid=4 > FAQ: http://www.thenetprofits.co.uk/coldfusion/faq > Structure your ColdFusion code with Fusebox. Get the > official book at http://www.fusionauthority.com/bkinfo.cfm > Unsubscribe: http://www.houseoffusion.com/cf_lists/uns > ubscribe.cfm?user=633.558.4 s. isaac dealey 954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
