SQL 按没有时间的日期分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30988419/
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
Group by date without time
提问by user3712641
I was wondering whether there was a way to group dates that are 2014-01-26 05:39:29.000 and 2014-01-26 07:45:31.000 into one day when counting them. I currently have the following code that just groups them by their unique datetime.
我想知道是否有一种方法可以将 2014-01-26 05:39:29.000 和 2014-01-26 07:45:31.000 的日期分组为一天。我目前有以下代码，仅按其唯一的日期时间将它们分组。
SELECT ETK_ExpirationDateTime, COUNT(*) as TotalRows
FROM History_Action
WHERE [State] = 4
GROUP BY ETK_ExpirationDateTime
ORDER BY ETK_ExpirationDateTime
Is there a cast or something I can do to make those 2 dates above appear as one row with a total sum?
是否有演员表或我可以做些什么来使上面的这两个日期显示为一行，总和？
回答by KrazzyNefarious
SELECT CAST(ETK_ExpirationDateTime AS DATE) AS DATE, COUNT(*) as TotalRows
FROM History_Action
WHERE [State] = 4
GROUP BY CAST(ETK_ExpirationDateTime AS DATE)
ORDER BY 1
回答by dotnetom
You can use conversion to date:
您可以使用转换为日期：
SELECT CONVERT(date, ETK_ExpirationDateTime) as ExpirationDateTime, COUNT(*) as TotalRows
FROM History_Action
WHERE [State] = 4
GROUP BY CONVERT(date, ETK_ExpirationDateTime)
ORDER BY CONVERT(date, ETK_ExpirationDateTime)
This only works for SQL 2008 or newer. For older versions of SQL you can use some tricky manipulation like this:
这仅适用于 SQL 2008 或更新版本。对于旧版本的 SQL，您可以使用一些棘手的操作，如下所示：
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, ETK_ExpirationDateTime)) as ExpirationDateTime, COUNT(*) as TotalRows
FROM History_Action
WHERE [State] = 4
GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, ETK_ExpirationDateTime))
ORDER BY DATEADD(dd, 0, DATEDIFF(dd, 0, ETK_ExpirationDateTime))
