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.

accepting one of the answers would be nice (*only the asker can do that*) — Paul Maxwell, Oct 28 '17 at 21:48
See also [How to group time by hour or by 10 minutes ](https://stackoverflow.com/questions/5002661/how-to-group-time-by-hour-or-by-10-minutes/41944083#41944083) — Michael Freidgeim, Jun 29 '22 at 03:29

score 168 · Answer 1 · answered Sep 14 '11 at 13:27

168

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

answered Sep 14 '11 at 13:27

JNK

63,321
15
122
138

11

This will only work in SQL Server 2008+ (when the Date datatype was introduced). If you're in 2005 or earlier, changing the cast to convert(convert(int, Datetimefield), datetime) should work. – Derek Sep 14 '11 at 13:30
I'm getting an error saying that 'Type Date is not a defined system type'. – Steven Sep 14 '11 at 13:30
5

@Steven - then see Derek's comment. This is why it's a good idea to include your **VERSION** in the question. – JNK Sep 14 '11 at 13:31
@Derek this doesn't seem to work. A `select convert(convert(int, getdate()), datetime)` fails with wrong syntax error – rabudde Sep 14 '11 at 13:44
1

sorry, convert(datetime, convert(int, getdate())) – Derek Sep 14 '11 at 13:56
SQL Server 2022 has a potentially more efficient method https://stackoverflow.com/a/76761752/73226 – Martin Smith Jul 25 '23 at 10:24

score 25 · Answer 2 · answered Sep 14 '11 at 13:31

25

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)

answered Sep 14 '11 at 13:31

rabudde

7,498
6
53
91

In older versions like 2000/2005 varchar conversion for this need is slower than use of `DATEADD(dd, DATEDIFF(dd, 0, date_field),0)` – Paul Maxwell Oct 28 '17 at 21:46

score 9 · Answer 3 · edited May 04 '17 at 20:23

9

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;

edited May 04 '17 at 20:23

Carol

1,852
26
29

answered May 21 '15 at 18:24

Jefferson Silva

235
2
8

1

This will work in any version of SQL Server and is faster than using varchar conversions. – Paul Maxwell Oct 28 '17 at 21:44
Could you clarify what is "dd"? I don't understand what you are supposed to put in it. – BelovedFool Oct 22 '18 at 13:24
"dd" is datepart equivalent to day. You can also use "day", "d" or "dd" – Jefferson Silva Nov 05 '18 at 14:34
For future readers: In case you adopt this good solution to rounding in seconds you will get an int overflow. That can be corrected by starting not from date "0", but a fixed date in the past that won't be accessed. Perhaps use the start of the year 2K. – Martin Jul 19 '23 at 14:09

score 3 · Answer 4 · edited Jan 15 '14 at 00:02

-- 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

score 2 · Answer 5 · answered May 11 '18 at 07:06

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;

score 0 · Answer 6 · answered Jul 25 '23 at 10:24

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)

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

6 Answers6

Linked

Related