Supposing we have the following records in an SQL Server table.
Date
19/5/2009 12:00:00 pm
19/5/2009 12:15:22 pm
20/5/2009 11:38:00 am
What is the SQL syntax for getting something like this one?
Date Count
19/5/2009 2
20/5/2009 1
You need to do any grouping on a Date only version of your datefield, such as this.
SELECT
CONVERT(VARCHAR(10), YourDateColumn, 101),
COUNT(*)
FROM
YourTable
GROUP BY
CONVERT(VARCHAR(10), YourDateColumn, 101)
I usually do this though, as it avoids conversion to varchar.
SELECT
DATEPART(yy, YourDateColumn),
DATEPART(mm, YourDateColumn),
DATEPART(dd, YourDateColumn),
COUNT(*)
FROM
YourTable
GROUP BY
DATEPART(yy, YourDateColumn),
DATEPART(mm, YourDateColumn),
DATEPART(dd, YourDateColumn)
EDIT: Another way to get just the date part of a datetime
DATEADD(d, 0, DATEDIFF(d, 0, YourDateColumn))
That would depend on your database engine. For SQL Server 2008 (and future versions), you can use the date
type to do this.
select
convert(date, date_column_name) as Date,
count(1) as Count
from table_name
group by convert(date, date_column_name)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With