How to Group by Month in T-SQL

Problem:

You'd like to group records by month in a SQL Server database.

Example:

Our database has a table named furniture with data in the columns id, name, and production_timestamp.

Solution:

You can use two DATEPART() functions to group records in a table by month and year.

Here's the query you would write:

SELECT
  DATEPART(YEAR, production_timestamp) AS year,
  DATEPART(MONTH, production_timestamp) AS month,
  COUNT(id) AS count
FROM furniture
GROUP BY
  DATEPART(MONTH, production_timestamp),
  DATEPART(YEAR, production_timestamp);

Here’s the result of the query:

Discussion:

Grouping records by month is a very common operation in SQL Server. In our example, the number of products is totaled for each month of the year. You usually want to take into account both the year and the month of the date, i.e. you want to group events in January 2022 separately from events in January 2021.

To group data by month in SQL Server, use the DATEPART() function. It extracts the given part (year, month, etc.) from the date. We use the function twice: once with the MONTH argument and once with the YEAR argument. This extracts the month and year (respectively) from the production timestamp. We combine the two calls to DATEPART() in the GROUP BY clause to group furniture by the month and year when they were produced.

We use the same two calls to DATEPART() in the SELECT clause to display the label for the year and month.