Menu
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.
862
1 Answer. You can use DATE_FORMAT operator. If you are using this you can easily group the date, timestamp or datetime column using whatever format you want.
Cast the datetime to a date, then GROUP BY using this syntax: SELECT SUM(foo), DATE(mydate) FROM a_table GROUP BY DATE(a_table. mydate);
SELECT CAST(Datetimefield AS DATE) as DateField, SUM(intfield) as SumField FROM MyTable GROUP BY CAST(Datetimefield AS DATE) 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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
