SQL Server GROUP BY datetime ignores hourly minute and selects with date and sum

Question

I have a table with two fields - datetime and int . I want to make a group on datetime only for a date that ignores the hour and minute. The SELECT must return a date that matches the int in one day.

Answer 1

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

Answer 2

Since it did not indicate which version of the SQL server it was using (the date type was not available in 2005), you can 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 to investigate the options that I would need to make, however, I believe that 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 it because the data type and format remain compatible with the date 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