SQL 如何在不考虑时间的情况下按日期时间列分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6054144/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I group by date time column without taking time into consideration
提问by The Muffin Man
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:00should be grouped with 3/8/2010 4:15:00
3/8/2010 7:42:00应该与 3/8/2010 4:15:00
回答by Oded
Cast/Convertthe values to a Datetype for your group by.
GROUP BY CAST(myDateTime AS DATE)
回答by Mitch Wheat
GROUP BY DATEADD(day, DATEDIFF(day, 0, MyDateTimeColumn), 0)
Or in SQL Server 2008onwards you could simply cast to Dateas @Oded suggested:
或者在SQL Server 2008之后，您可以简单地 Date按照@Oded 的建议进行转换：
GROUP BY CAST(orderDate AS DATE)
回答by Kamyar
In pre Sql 2008 By taking out the date part:
在 Sql 2008 之前通过取出日期部分：
GROUP BY CONVERT(CHAR(8),DateTimeColumn,10)
回答by Aravindh Gopi
GROUP BY DATE(date_time_column)
GROUP BY DATE(date_time_column)
回答by user3169774
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)
回答by Radhakrishnan
Here is the example works fine in oracle
这是示例在 oracle 中工作正常
select to_char(columnname, 'DD/MON/yyyy'), count(*) from table_name group by to_char(createddate, 'DD/MON/yyyy');
回答by Ruchira
CAST datetime field to date
CAST 日期时间字段到日期
select CAST(datetime_field as DATE), count(*) as count from table group by CAST(datetime_field as DATE);
回答by alireza
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 :
我相信你需要在一年中的那一天分组。那么为什么不使用 TRUNK_DATE 函数呢。它的工作方式如下所述：
Group By DATE_TRUNC('day' , 'occurred_at_time')
