Tuesday, June 29, 2010

SQL Server – Grouping By Date

If you have a dateTime column in your database and you need to group by it, then here is a quick way to do it:

hint: Use the Convert method with an appropriate style number so that you can get rid of the time value. (in my case I used 111, which output yyyy/mm/dd).

SELECT distinct convert(varchar,[dateColumn],111) as date, count(1) as count
FROM [tableName]
where [dateColumn] >= '06-01-2010'
group by convert(varchar,[dateColumn],111)
order by date

No comments: