Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Server query - Count distinct DateTime field

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

like image 609
OrElse Avatar asked May 20 '09 13:05

OrElse


2 Answers

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))
like image 161
Robin Day Avatar answered Oct 05 '22 20:10

Robin Day


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)
like image 20
Adam Robinson Avatar answered Oct 05 '22 20:10

Adam Robinson