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
-- 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
datepart() function has the same syntax as the
datename() function. Therefore, both functions can be used in the same way.