SQLTeam.com | Weblogs | Forums

Select Help


#1

I have a table full of records which has ID, TopicID, UserID, SubmitTime etc.

I have a column which is of type ‘DateTime’ (SubmitTime). I want to retrieve all my records and group them by date only (ignore time) and order them in a descending order of date.

How can I do this?


#2
SELECT SubmitDate = DATEADD(DAY, DATEDIFF(DAY, 0, SubmitTime), 0), COUNT (*)
FROM    aTable
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, SubmitTime), 0)
ORDER BY SubmitDate DESC

#3

SELECT SubmitDate = CAST(SubmitTime AS date), COUNT(*)
FROM aTable
GROUP BY CAST(SubmitTime AS date)
ORDER BY SubmitDate DESC;