How to group a resultset by day?

Question

I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day going back 3 months?

I am using datediff but it would be great if I can have a field with the actual date and a count in the resultset.

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())

Answer 1

For starters, why are you reversing your fields in the datediff?

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())

Number two, why does this not work for you?

SELECT crDate, COUNT(*)
FROM tracexml
GROUP BY crDate

Answer 2

How about the following. I even added a few extra columns if you wanted to put it into a nifty little pivot table where you could group by year, month, day

SELECT 
    CONVERT(VARCHAR(10),crDate,120) as [crDate]
    ,datePart(yy,CONVERT(VARCHAR(10),crDate,120) )  as [crYear]
    ,datePart(mm,CONVERT(VARCHAR(10),crDate,120) )  as [crMonth]
    ,datePart(dd,CONVERT(VARCHAR(10),crDate,120) )  as [crDay]
    , COUNT(*) as [traceCount]
FROM tracexml
WHERE crDate > dateAdd(d,-30,CONVERT(VARCHAR(10),getDate(),120))
GROUP BY CONVERT(VARCHAR(10),crDate,120)
    ,datePart(yy,CONVERT(VARCHAR(10),crDate,120) )  
    ,datePart(mm,CONVERT(VARCHAR(10),crDate,120) )  
    ,datePart(dd,CONVERT(VARCHAR(10),crDate,120) )