Back to cookbooks list Articles Cookbook

How to Group by Month in T-SQL

Table of Contents

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.

idnameproduction_timestamp
1double bed2022-02-01 11:45:23
2coffee table2022-02-01 11:46:13
3chest of drawers2022-01-22 17:22:05

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:

yearmonthcount
202211
202222

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.

Recommended courses:

Recommended articles:

See also: