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.