[cfaussie] Re: Grouping Output By Date Not working

[Claude Raiola]

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' <[EMAIL PROTECTED]> 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
-~----------~----~----~----~------~----~------~--~---