I am trying to use SQL to count the number of rows for each date in my database

My database is something like this:

Date                        Col2         Col3       Col4
2020-11-02 06:45:16.000     data         data       data
2020-11-02 07:23:23.111     data         data       data
2020-12-14 08:55:44.213     data         data       data

I only need the counts of the dates like this:

Date          Count
2020-11-02     2
2020-12-14     1

I’ve tried …

select distinct(Date),
       count(*)

from database

… but the date format is not my friend. Any ideas would be appreciated.

Answer

In most databases, you can remove the date component by converting to a date and using group by:

select cast(Date as date), count(*)    
from database
group by cast(Date as date)
order by cast(Date as date);

However, various databases also have other methods to do the conversion, such as:

• date(date) in MySQL.
• trunc(date) in Oracle.
• date_trunc('day', date) in Postgres.