Search code examples
sqlsql-serversql-server-2000

Grouping by date when datetime is given

In my table, I have some ID's associated with each Date. How do I count the number of ID's associated with a date ? The date column is in datetime, but I want to group by date and not date time.

Expected output - 

11/14/2013, 30 counts
11/15/2013, 400 counts 
etc

I am using SQL server 2000.

Solution

  • Group by DATEADD(day, DATEDIFF(day, 0, DateTimeCol), 0) which truncates the time to midnight.

    SELECT Date = DATEADD(day, DATEDIFF(day, 0, DateTimeCol), 0)
     , COUNT = COUNT(*)
FROM dbo.TableName
GROUP BY DATEADD(day, DATEDIFF(day, 0, DateTimeCol), 0)