Next & Previous

Aggregate SQL records by date

This is Ste’s way of doing it, and if you don’t like it, well… you can email me or something. Sorry. Thanks.

Say you have a [Created] column which is a datetime2. You just want the date part.

My secret sauce is convert(date, [Created]).

Now you can aggregate by day, and sort by day or volume, like:

count(*) as CountLogs, 
convert(date, [Created]) LogDate
from SomeTable
group by convert(date, [Created])
-- order by convert(date, [Created]) desc
-- (or)
-- order by CountLogs desc

I did two little posts today 😎 See also Delete a load of rows from a slow SQL table!.

🏎🍂💨 Now we’re blogging!