SQL Server Server query - Count distinct DateTime field

Question

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

Answer 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))

Answer 2

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)

Answer 3

Depends on your DBMS. Example for Mysql:

SELECT DATE_FORMAT(dateColumn, '%e/%c/%Y') as `date`, COUNT(*)
FROM YourTable
GROUP BY `date`

Answer 4

What RDBMS are you on? Using Sybase, your query would look like this:

select date(datetimeColumn) as myDate, count(*) as myTotal
from thisTable
Group by myDate
Order by myTotal, myDate

Answer 5

After Googling found this one too...

SELECT CAST(FLOOR(CAST(Expr1 AS FLOAT)) AS DATEtime) AS Expr1,

COUNT(*) AS Expr2

FROM MY_TABLE

GROUP BY

CAST(FLOOR(CAST(Expr1 AS FLOAT)) AS DATEtime)

The cons?

1. High speed execution
2. The results returned are in the original locale. Ex for Greek 19/5/2009

Thank you all