Are you doing is on a query of a query.
Won work, sql only funions. Regards Dale Fraser http://learncf.com <http://learncf.com/> http://flexcf.com <http://flexcf.com/> From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Claude Raiola Sent: Thursday, 4 September 2008 10:04 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Re: Grouping Output By Date Not working I have tried the suggestion below however it generates the following error message Query Of Queries syntax error. Encountered "CONVERT. Incorrect Select List, Incorrect select column, Some background: The starttime is being extracted from CFExchangeCalendar from each calendar event found. I am then querying the data as follows <CFQUERY NAME="mycalendar1" dbtype="query"> select starttime, CONVERT(DATE, YEAR(starttime) + '-' + MONTH(starttime) + '-' + DAY(starttime)) AS startdate, [from] as sentfrom, endtime, subject, duration, location, message, organizer, htmlmessage, requiredattendees from mycalendar group by startdate starttime, endtime, subject, sentfrom, duration, location, message, organizer, htmlmessage, requiredattendees order by starttime </cfquery> Regards Claude Raiola B.Econ (Acc), B.Hot.Mngt. Websites: www.AustralianAccommodation.com www.SAMARIS.NET www.WebSiteSolutions.com.au Mobile: 0414 228 948 On Thu Sep 4 3:30 , 'Steve Onnis' sent: Claude You will need to compile your own date as part of the query like select starttime, CONVERT(DATE, YEAR(startdate) + '-' + MONTH(startdate) + '-' + DAY(startdate)) AS startdate, [from] as sentfrom, endtime, subject, duration, location, message, organizer, htmlmessage, requiredattendees from mycalendar group by startdate, starttime, endtime, subject, sentfrom, duration, location, message, organizer, htmlmessage, requiredattendees order by startdate _____ From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of Claude Raiola Sent: Wednesday, 3 September 2008 5:59 PM To: cfaussie@googlegroups.com Subject: [cfaussie] Grouping Output By Date Not working I am trying to group my cfquery output by date however as the values the date column also include the time stamp this is causing a problem. Is there a way that i can make the query only group by the date portion of the datetime stamp as this will then allow me to group all records of the same date together and then sort said grouped records by the complete date value which includes the date value. select starttime, [from] as sentfrom, endtime, subject, duration, location, message, organizer, htmlmessage, requiredattendees from mycalendar group by starttime, endtime, subject, sentfrom, duration, location, message, organizer, htmlmessage, requiredattendees order by starttime Regards Claude Raiola B.Econ (Acc), B.Hot.Mngt. Websites: www.AustralianAccommodation.com www.SAMARIS.NET www.WebSiteSolutions.com.au Mobile: 0414 228 948 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en -~----------~----~----~----~------~----~------~--~---
