Tuesday, December 27, 2011

SQL to ORDER BY datetime Field by date

It's tricky to order by a datetime field by date because the GROUP BY clause attempts to group by seconds instead of days. My solution is:

SELECT
distinct Convert(varchar(10),Tasks.DateCreated,101) As Date,
Count(Convert(varchar(10),Tasks.DateCreated,101)) As DateCount
FROM Tasks
GROUP BY Convert(varchar(10),Tasks.DateCreated,101)