Group by month in SQL Server: CONVERT, FORMAT and DATEFROMPARTS

Finance always wants it by month. The trick is turning each date into one value per month, then grouping by that exact expression.

CONVERT to yyyy-mm text

SELECT CONVERT(char(7), order_date, 120) AS month,
       SUM(total_amount) AS revenue
FROM Orders
WHERE status = 'Shipped'
GROUP BY CONVERT(char(7), order_date, 120)
ORDER BY month;

Style 120 formats a date as yyyy-mm-dd hh:mi:ss. A char(7) only has room for the first seven characters: 2026-03. Text in this format sorts in date order, which is handy.

FORMAT

SELECT FORMAT(order_date, 'yyyy-MM') AS month, SUM(total_amount) AS revenue
FROM Orders
GROUP BY FORMAT(order_date, 'yyyy-MM');

Easier to read, and you can write 'MMM yyyy' for “Mar 2026”. It is noticeably slower on large tables, so prefer CONVERT for big reports.

Keep it a real date

SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS month_start,
       SUM(total_amount) AS revenue
FROM Orders
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1);

This returns the first of each month as a date, which Excel and Power BI treat as a date rather than text. On SQL Server 2022 you can write DATETRUNC(month, order_date) instead.