How to group a resultset by day? 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())
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