How can I group by date time column without taking time into consideration
SqlSql ServerTsqlSql Server-2008Sql Problem Overview
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
Sql Solutions
Solution 1 - Sql
Cast/Convert the values to a Date type for your group by.
GROUP BY CAST(myDateTime AS DATE)
Solution 2 - Sql
GROUP BY DATEADD(day, DATEDIFF(day, 0, MyDateTimeColumn), 0)
Or in SQL Server 2008 onwards you could simply cast to Date as @Oded suggested:
GROUP BY CAST(orderDate AS DATE)
Solution 3 - Sql
In pre Sql 2008 By taking out the date part:
GROUP BY CONVERT(CHAR(8),DateTimeColumn,10)
Solution 4 - Sql
GROUP BY DATE(date_time_column)
Solution 5 - Sql
CAST datetime field to date
select CAST(datetime_field as DATE), count(*) as count from table group by CAST(datetime_field as DATE);
Solution 6 - Sql
Here's an example that I used when I needed to count the number of records for a particular date without the time portion:
select count(convert(CHAR(10), dtcreatedate, 103) ),convert(char(10), dtcreatedate, 103)
FROM dbo.tbltobecounted
GROUP BY CONVERT(CHAR(10),dtcreatedate,103)
ORDER BY CONVERT(CHAR(10),dtcreatedate,103)
Solution 7 - Sql
Here is the example works fine in oracle
select to_char(columnname, 'DD/MON/yyyy'), count(*) from table_name group by to_char(createddate, 'DD/MON/yyyy');
Solution 8 - Sql
Well, for me it was pretty much straight, I used cast with groupby:
Example:
Select cast(created_at as date), count(1) from dbname.tablename GROUP BY cast(created_at as date)
Note: I am using this on MSSQL 2016.
Solution 9 - Sql
I believe you need to group by , in that day of the month of the year . so why not using TRUNK_DATE functions . The way it works is described below :
Group By DATE_TRUNC('day' , 'occurred_at_time')