SQL Server GROUP BY datetime 忽略小时分钟以及带有日期和总和值的选择

Question

我有一个包含两个字段的表 - datetime 和 int。我只想对日期进行分组，忽略小时和分钟。 SELECT 语句应返回一个映射到一天的 int 总和的日期。

Answer 1

SELECT CAST(Datetimefield AS DATE) as DateField, SUM(intfield) as SumField
FROM MyTable
GROUP BY CAST(Datetimefield AS DATE)

Answer 2

由于他没有指定他使用哪个版本的 SQL Server（date 类型在 2005 年不可用），因此也可以使用

SELECT CONVERT(VARCHAR(10),date_column,112),SUM(num_col) AS summed
FROM table_name
GROUP BY CONVERT(VARCHAR(10),date_column,112)

Answer 3

我来研究我必须这样做的选项，但是，我相信我使用的方法是最简单的：

SELECT COUNT(*), 
       DATEADD(dd, DATEDIFF(dd, 0, date_field),0) as dtgroup 
FROM TABLE 
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, date_field),0) 
ORDER BY dtgroup ASC;

Answer 4

-- 我喜欢这个，因为数据类型和格式与日期时间数据类型保持一致

;with cte as(
    select 
        cast(utcdate as date) UtcDay, DATEPART(hour, utcdate) UtcHour, count(*) as Counts
    from dbo.mytable cd 
    where utcdate between '2014-01-14' and '2014-01-15'
    group by
        cast(utcdate as date), DATEPART(hour, utcdate)
)
select dateadd(hour, utchour, cast(utcday as datetime)) as UTCDateHour, Counts
from cte

Answer 5

就我个人而言，我更喜欢格式功能，它允许您非常轻松地更改日期部分。

     declare @format varchar(100) = 'yyyy/MM/dd'
     select 
        format(the_date,@format), 
        sum(myfield) 
     from mytable 
     group by format(the_date,@format) 
     order by format(the_date,@format) desc;

Answer 6

对于 SQL Server 2022+，您可以使用 DATETRUNC。

与其他方法相比，它的优势在于它能够利用按 datetime 排序的索引也按 date 排序的事实，并且可以避免排序操作。

例如，比较下面的

CREATE TABLE #T(
Datetimefield DATETIME,
intfield INT,
)

CREATE INDEX IX ON #T(Datetimefield) INCLUDE (intfield)

SELECT CAST(Datetimefield AS DATE) AS DateField,
       SUM(intfield)               AS SumField
FROM   #T
GROUP  BY CAST(Datetimefield AS DATE) 

SELECT DATETRUNC(DAY,Datetimefield) AS DateField,
       SUM(intfield)               AS SumField
FROM   #T
GROUP  BY DATETRUNC(DAY,Datetimefield)