Parsing Date out of datetime column

[jalbao]

What I am trying to do is use an aggregate SQL function
to determine what days had the most entries to a contest.

Since the db column is a 'datetime' field, the aggregate
function is looking at the date and the time as one unique
value. I want it to COUNT only the DATE values.

My results therefore are this:
CREATIONDATE COUNT
-----------------------------
2002-01-02 18:00:05 2
2002-01-02 14:01:37 1
2002-01-02 14:00:28 1
2002-01-02 13:59:44 1
2002-01-03 13:58:05 1

When I want:
CREATIONDATE COUNT
-------------------
2002-01-02 256
2002-01-03 118
2002-01-02 34

any ideas/help?

tia

 

[tlbroadbent]

Select
Convert(char(10), CreationDate, 120) As CreationDate,
Sum(Count)
From Table
Group By Convert(char(10), CreationDate, 120) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jalbao]

thanks terry - you once again have made my day...

btw: i think you made a typo so i am posting the correction for future users that may need this solution.

Select Convert(char(10), CreationDate, 120) As CreationDate, Count(CreationDate)
From TABLE
Group By Convert(char(10), CreationDate, 120)

 

[tlbroadbent]

I agree. I assumed the table had a column called count but I see that isn't correct. I would add an alias for the count column.

Select
Convert(char(10), CreationDate, 120) As CreationDate,
Count(CreationDate) As Cnt
From TABLE
Group By Convert(char(10), CreationDate, 120)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.