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

Sql ServerDatetimeSelectGroup By

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionStevenView Question on Stackoverflow
Solution 1 - Sql ServerJNKView Answer on Stackoverflow
Solution 2 - Sql ServerrabuddeView Answer on Stackoverflow
Solution 3 - Sql ServerJefferson SilvaView Answer on Stackoverflow
Solution 4 - Sql ServerSqlHogView Answer on Stackoverflow
Solution 5 - Sql ServerKrikView Answer on Stackoverflow