It depends on the database, of course. Assuming you're using some
version of SQL Server (or Sybase) you might want to try something like:
SELECT logdate, convert(char(14), logdate, 111) as doy,
COUNT(*) as count_ttl
FROM customer_log
WHERE logdate BETWEEN #start# AND #end#
GROUP BY convert(char(14), logdate, 111)
ORDER BY logdate
Chuck McElwee
Macromedia Certified Advanced ColdFusion Developer
www.etechsolutions.com
[EMAIL PROTECTED]
-----Original Message-----
From: S. Isaac Dealey [mailto:[EMAIL PROTECTED]]
Sent: Thursday, August 29, 2002 6:08 PM
To: CF-Talk
Subject: Re: group by date question
> I'm trying to group my query results by day. The field
> I'm grabbing from
> has a date/time stamp in it so what I'm getting are
> results grouped by time.
> I tried to fix this with this code which obviously doesn't
> work. How can I
> group this by date and ignore the time stamp?
> <cfquery name="contact_count_ttl"
> datasource="#heinz.dsn#">
> SELECT logdate, DATEPART(dy, logdate) as doy, COUNT(*) as
> count_ttl
> FROM customer_log
> WHERE logdate BETWEEN #start# AND #end#
> GROUP BY doy
> ORDER BY logdate
> </cfquery>
I remember somebody else on the list saying you could group by
convert(datetime,convert(int,doy)) and that would convert the date to an
integer representation of the day and then back to a datetime value and
truncate the time from the date... Or maybe he was using CAST ... I
don't
remember precisely, but it sounded to me like a really elegant hack. :)
In
the past I've generally added a 2nd date column to the table and simply
inserted the date from coldfusion discluding the time. Which from the db
perspective is liable to be more efficient, although if the date column
ever
gets updated outside of CF you might wind up with a situation where the
two
date columns don't match.
Isaac
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
