SQL Server GROUP BY datetime ignore hour minute and a select with a date and sum value

Question

I have a table with two fields - datetime and int. I want to do a group by on the datetime only on the date ignoring the hour and minute. The SELECT statement should return a date that maps to the sum of the int of a single day.

Answer 1

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

Answer 2

As he didn't specify which version of SQL server he uses (date type isn't available in 2005), one could also use

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

I came researching the options that I would have to do this, however, I believe the method I use is the simplest:

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

-- I like this as the data type and the format remains consistent with a date time data type

;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

Personally i prefer the format function, allows you to simply change the date part very easily.

     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

For SQL Server 2022+ you can use DATETRUNC.

This has an advantage over the other methods in that it is able to take advantage of the fact that an index ordered by datetime is also ordered by date and can avoid a sort operation.

e.g. Compare the below

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)