Introducing PopSQL + dbt. The first SQL editor with built-in dbt supportRead more →

How to Group by Time in SQL Server 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
  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.

Ready for a modern SQL editor?