SQL Server GROUP BY datetime ignore hour minute and a select with a date and sum value
Sql ServerDatetimeSelectGroup BySql Server Problem Overview
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.
Sql Server Solutions
Solution 1 - Sql Server
SELECT CAST(Datetimefield AS DATE) as DateField, SUM(intfield) as SumField
FROM MyTable
GROUP BY CAST(Datetimefield AS DATE)
Solution 2 - Sql Server
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)
Solution 3 - Sql Server
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;
Solution 4 - Sql Server
-- 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
Solution 5 - Sql Server
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;