How to Group by Day, Date, Hour, Month or Year in SQL Server

When you want to group by minute, hour, day, week, etc., you may be tempted to just group by your timestamp column.

If you do that, though, you'll get one group per second -- likely not what you want. Instead, you should “truncate” your timestamp to the granularity you want, like minute, hour, day, week, etc. The functions you need here are datepart() and datename():

-- returns number of sessions grouped by particular timestamp fragment
select
  max(start_date) as session_start_date,
  count(id) as number_of_sessions
from [dbo].[sessions]
-- or datename()
group by
  datepart(year, start_date),
  datepart(month, start_date), -- omit if grouping by year
  datepart(week, start_date), -- omit if grouping by month
  datepart(day, start_date), -- omit if grouping by week
  datepart(hour, start_date), -- omit if grouping by day
  datepart(minute, start_date); -- omit if grouping by hour

The datepart() function has the same syntax as the datename() function. Therefore, both functions can be used in the same way.