Hi Claude,
Here's an example of some code that I use to report activity summarised by
day...
Select count(*) as pageCount, dayOfMonth(activityDate) as dayOfMonth,
activityDate
from Activity
Where activityDate #createODBCDate(startDate)#
And activityDate #createODBCDate(endDate)#
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,
If you're using Oracle then you can simple trunc the date time stamp. Might
work for SQL Server too.
On 03/09/2008, Claude Raiola [EMAIL PROTECTED] wrote:
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
Hi,
to remove the time from a date column I would use (SQL 2000\2005 ETC)
SET DATEFORMAT DMT
SELECT
CONVERT(DATETIME, CONVERT(VARCHAR, MY_DATE_TIME_FIELD, 103))
FROM
XXX
GROUP BY
CONVERT(DATETIME, CONVERT(VARCHAR, MY_DATE_TIME_FIELD, 103))
On 03/09/2008 at 5:58 pm, in message
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
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
ie] 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 ext
Hi Claude,
If QoQ, try using the CAST function.
http://livedocs.adobe.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/wwhelp.htm?context=ColdFusion_Documentationfile=1271.htm
I haven't tested the following, just guessing what it might be.
If it doesn't work, play around with it and/or
I think the CAST to DATE in query of queries gives a timestamp which
still has the time part, so here's another suggestion:
It's not the most efficient way, but you could try pre-processing the
query data like this so that your startdate value only contains the
date part:
cfset