grouping output

[namtax]

hi i have an update page, which shows all the activity of your friends on my website..

here it is

http://www.musicexplained.co.uk/delete/updates.cfm?u=namtax&friends"

at the moment its displaying what your friends have done at any date....so you see for example

artists your friends have added to the database

gas added Whitehouse to the musicexplained database on 18-Apr-07

blanko added to the musicexplained database on 21-Apr-07

stag added atest to the musicexplained database on 22-Apr-07

what i would like to achieve...is to group the activity by date...so instead you would get


artists your friends have added to the database

18/April/07

gas added Whitehouse to the musicexplained database

21/April/07

blanko added to the musicexplained database

22/Apr/07

stag added atest to the musicexplained database

i have tried to group the output of the query, so you get

<cfoutput query="friends_activity" group="date_fav(date the person added an artist as a friend)">
on #DATEFORMAT(date_fav)#

<p>
<cfoutput>
#name_usr#(user) added
#name_art#(artist they added to their favourites) to their favourites
</cfoutput>
</p>
</cfoutput>

but the info is not grouping by day properly..

the issue i think, is that the date info initially being input into the database contains the time as well as the date, so the date_fav column im trying to group by isnt grouping by day like i want it to...its grouping by the date and the time...

is there anyway i can group by the day itself not the time...

i hope this makes sense..

any help would be appreciated

[Dan_Bracuk]

What does the order by clause in your query look like?

[namtax]

hi dan, this is my current query

<cfquery name="art_fav" datasource="#application.datasource#">
SELECT name_usr, name_art, date_fav
FROM (user_usr
LEFT OUTER JOIN artists_fav
ON user_usr.id_usr = artists_fav.id_usr_fav)
LEFT OUTER JOIN artist_art
ON artist_art.id_art = artists_fav.id_artkey_fav
WHERE id_usr_fav IN
(SELECT id_usr_friend
FROM user_friend
WHERE id_main_friend = #session.auth.idnumber#
AND update_friend = 1)
AND id_usr <> #session.auth.idnumber#
ORDER BY date_fav
</cfquery>

thanks again

[Dan_Bracuk]

You probably identified the cause correctly when you mentioned the time portion of the date field. Many dbs have functions that convert dates to text. An example would be oracle's to_char function.

[craigkaminsky]

Along the lines of Dan's last post, here's a Transact SQL statement you can run in a select clause to

SELECT CAST(date_fav AS smalldatetime) as mydate

One quick note, if you don't need to record the time in your date column, you can use the SQL Server data type: smalldatetime. This records only the date and not the time (i.e., Jan 1 2007).

[namtax]

ok thanks
i will have a look at a similar function for my mysql database

cheers