Hi Greg,
You can use DATEPART to extract the parts of the date i.e. DATEPART(month,YourDateColumn), DATEPART(year, YourDateColumn). DATEPART has a wide variety of options but SQL Server also has direct functions for some of these i.e. MONTH(YourDateColumn), YEAR(YourDateColumn). You can group by the outcome of these functions just like any other column. I suspect you might also want to look at the outcome of adding WITH ROLLUP and/or WITH CUBE to your queries. Regards, Greg Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On Behalf Of Greg Keogh Sent: Tuesday, 16 July 2013 8:34 AM To: ozDotNet Subject: [OT] T-SQL GroupBy and Sum on a DateTime I've got a SQL Server table containing logging data. All of the columns are numbers except for a single DateTime column. I want to generate totals by hour, day, month or year. Because the GroupBy column is a DateTime I can't figure out the query syntax to sum on Y, YM, YMD or YMDH combinations. I'll bet there's a neat trick to do this, does anyone know what it is? Thanks Greg K
