How can I group by date time column without taking time into consideration

Question

I have a bunch of product orders and I'm trying to group by the date and sum the quantity for that date. How can I group by the month/day/year without taking the time part into consideration?

3/8/2010 7:42:00 should be grouped with 3/8/2010 4:15:00

Answer 1

You can use the following SQL Query to do group by date time column:

SELECT count (CAST (my_DateTime AS DATE))

FROM dbo.table_name

GROUP BY CAST (my_DateTime AS DATE)

or you can also use CONVERT instead of CAST like following:

SELECT count (CONVERT(CHAR(8),DateTimeColumn,10))

FROM dbo.table_name

GROUP BY CONVERT(CHAR(8),DateTimeColumn,10)

I recommend this SQL Certification course if you wish to learn writing SQL queries and check out this SQL tutorial to learn more about CAST and CONVERT functions.

Answer 2

A method that returns only the date segment can be used to aggregate by a date column while disregarding the time element.

 The method for doing this with Python (Pandas) and SQL is as follows:

SELECT DATE(x) AS y, COUNT(*)

FROM your_table

GROUP BY y;

Python (Pandas) Example:

df['y'] = df['x'].dt.date

df.groupby('y').size()

In both examples, x is the datetime column, and y is the new column grouping by date only.