Count and Group By with DateTime!

Hi all,

I have a problem with my query which is suppose to select count posts group by the date, the query works but doesnt return the count as i want, i think the problem that the datetime column contains also Time in hours which ofcourse isnt same in all rows in same day, so i dont know what to do, Here's my Query:

SELECT

PostID,Date,COUNT(Date)AS'TotalPosts'
FROMPosts
GROUP BYPostID,Date

Thank you for help

I didn't have that table available but I tested the same query against Sales.SalesOrderDetail table in AdventureWorks sample database. I believe you get the idea from it

SELECT Count(*) TheCount, [Day]
FROM (
SELECT
CAST(
CAST(MONTH(ModifiedDate) AS varchar(2) ) + '/' +
CAST(DAY(ModifiedDate) AS varchar(2) ) + '/' +
CAST(YEAR(ModifiedDate) AS varchar(4) )
AS datetime
)
as [Day]
FROM Sales.SalesOrderDetail

) TMP
GROUP BY [Day]
ORDER BY [Day]

Just change the table and field names respectively in the query

|||Amazing, Thank you alot