SQL Extract Month From Date (and Group by Month)
· 6 min read
To extract the month from a date in SQL, use EXTRACT(MONTH FROM your_date). It returns the month as a number from 1 to 12. This is standard SQL and works in Postgres, MySQL, DuckDB, Oracle, BigQuery and Snowflake. SQL Server and SQLite use their own functions, listed further down.
SELECT order_id, order_date,
EXTRACT(MONTH FROM order_date) AS order_month
FROM orders
ORDER BY order_id;If you want to group by month, you almost always need the year too, or a function like DATE_TRUNC that keeps it. The rest of this guide shows why, with real output.
A worked example
| order_id | order_date | amount |
|---|---|---|
| 1 | 2024-12-15 | 200 |
| 2 | 2025-01-03 | 120 |
| 3 | 2025-01-20 | 80 |
| 4 | 2025-02-11 | 150 |
| 5 | 2025-03-02 | 60 |
| 6 | 2025-03-28 | 90 |
| 7 | 2025-12-05 | 300 |
The query above returns:
| order_id | order_date | order_month |
|---|---|---|
| 1 | 2024-12-15 | 12 |
| 2 | 2025-01-03 | 1 |
| 3 | 2025-01-20 | 1 |
| 4 | 2025-02-11 | 2 |
| 5 | 2025-03-02 | 3 |
| 6 | 2025-03-28 | 3 |
| 7 | 2025-12-05 | 12 |
Orders 1 and 7 both show 12. They are a year apart, but the month number alone cannot tell them apart.
How to extract year from date
EXTRACT takes other parts too: YEAR, QUARTER, DAY, DOW (day of week) and more.
SELECT order_id, order_date,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month
FROM orders
ORDER BY order_id;| order_id | order_date | order_year | order_month |
|---|---|---|---|
| 1 | 2024-12-15 | 2024 | 12 |
| 2 | 2025-01-03 | 2025 | 1 |
| 3 | 2025-01-20 | 2025 | 1 |
| 4 | 2025-02-11 | 2025 | 2 |
| 5 | 2025-03-02 | 2025 | 3 |
| 6 | 2025-03-28 | 2025 | 3 |
| 7 | 2025-12-05 | 2025 | 12 |
Group by month: revenue by month
To get revenue by month, group by both the year and the month:
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
COUNT(*) AS orders,
SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;| order_year | order_month | orders | revenue |
|---|---|---|---|
| 2024 | 12 | 1 | 200 |
| 2025 | 1 | 2 | 200 |
| 2025 | 2 | 1 | 150 |
| 2025 | 3 | 2 | 150 |
| 2025 | 12 | 1 | 300 |
Repeating the expressions in GROUP BY works everywhere. Postgres, MySQL and DuckDB also let you write GROUP BY order_year, order_month or GROUP BY 1, 2. Grouping on two columns is covered in more depth in GROUP BY multiple columns.
DATE_TRUNC: one column per month
DATE_TRUNC('month', order_date) rounds a date down to the first day of its month. The year stays attached, so a single column is enough to group by month safely:
SELECT DATE_TRUNC('month', order_date) AS month_start,
SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;| month_start | revenue |
|---|---|
| 2024-12-01 00:00:00 | 200 |
| 2025-01-01 00:00:00 | 200 |
| 2025-02-01 00:00:00 | 150 |
| 2025-03-01 00:00:00 | 150 |
| 2025-12-01 00:00:00 | 300 |
In Postgres and DuckDB, DATE_TRUNC returns a timestamp, hence the 00:00:00. Cast it if you want a plain date:
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE) AS month_start,
SUM(amount) AS revenue
FROM orders
GROUP BY 1
ORDER BY month_start;| month_start | revenue |
|---|---|
| 2024-12-01 | 200 |
| 2025-01-01 | 200 |
| 2025-02-01 | 150 |
| 2025-03-01 | 150 |
| 2025-12-01 | 300 |
A real date column sorts correctly, works on chart axes, and is easy to join to a calendar table. It is the best choice for most monthly reports. To turn it into a running total by month, see SQL running total.
Dialect differences
Date functions are where SQL dialects differ most. The month number and month-start equivalents:
| Database | Month number | First day of the month |
|---|---|---|
| Postgres | EXTRACT(MONTH FROM d) or DATE_PART('month', d) | DATE_TRUNC('month', d) |
| MySQL | MONTH(d) or EXTRACT(MONTH FROM d) | DATE_FORMAT(d, '%Y-%m-01') |
| SQL Server | MONTH(d) or DATEPART(month, d) | DATETRUNC(month, d) (2022+) or DATEFROMPARTS(YEAR(d), MONTH(d), 1) |
| SQLite | CAST(strftime('%m', d) AS INTEGER) | date(d, 'start of month') |
| DuckDB | EXTRACT(MONTH FROM d) or month(d) | DATE_TRUNC('month', d) |
| BigQuery | EXTRACT(MONTH FROM d) | DATE_TRUNC(d, MONTH) |
| Oracle | EXTRACT(MONTH FROM d) | TRUNC(d, 'MM') |
SQL Server has no EXTRACT, and SQLite's strftime('%m', d) returns text like '03', which is why it needs the cast. For a year-month label instead of a date, use TO_CHAR(d, 'YYYY-MM') in Postgres, DATE_FORMAT(d, '%Y-%m') in MySQL, FORMAT(d, 'yyyy-MM') in SQL Server or strftime(d, '%Y-%m') in DuckDB.
Common mistakes
Grouping by month without the year (the AI mistake to watch for)
Ask an AI assistant for "revenue by month" and it will very often group by the month number alone:
SELECT EXTRACT(MONTH FROM order_date) AS order_month,
SUM(amount) AS revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY order_month;| order_month | revenue |
|---|---|
| 1 | 200 |
| 2 | 150 |
| 3 | 150 |
| 12 | 500 |
December shows 500: December 2024 (200) and December 2025 (300) have been added together. On a dataset that only covers one year this is harmless, which is exactly why the bug survives. The moment the data spans two years, every month is wrong.
How to spot it: check the date range first (SELECT MIN(order_date), MAX(order_date) FROM orders). If it crosses a year boundary and the query groups by month without the year, the numbers are blended. Use DATE_TRUNC or group by year and month.
Sorting by month name
TO_CHAR(order_date, 'Month') or MONTHNAME(order_date) give nice labels, but sorting by them puts April first and September last, alphabetically. Sort by the month number or the month-start date and only display the name.
Filtering a month with EXTRACT in WHERE
WHERE EXTRACT(MONTH FROM order_date) = 1 works, but it matches January of every year, and the database cannot use an index on order_date. A date range is safer and faster:
SELECT order_id, order_date, amount
FROM orders
WHERE order_date >= DATE '2025-01-01'
AND order_date < DATE '2025-02-01'
ORDER BY order_id;| order_id | order_date | amount |
|---|---|---|
| 2 | 2025-01-03 | 120 |
| 3 | 2025-01-20 | 80 |
Using < the first day of the next month, rather than <= '2025-01-31', also catches timestamps late on the 31st.
Expecting empty months to appear
GROUP BY only returns months that have rows. In the example, April to November are missing, not zero. If a chart needs every month, left join your result to a list of months (a calendar table or generate_series in Postgres and DuckDB). See SQL LEFT JOIN.
FAQ
How do I get the month name from a date in SQL?
Postgres: TO_CHAR(d, 'Month'). MySQL: MONTHNAME(d). SQL Server: DATENAME(month, d). DuckDB: monthname(d).
How do I extract year and month from a date in SQL?
Either two columns with EXTRACT(YEAR ...) and EXTRACT(MONTH ...), or one column with DATE_TRUNC('month', d) or a 'YYYY-MM' label as in the dialect section.
Does EXTRACT work on timestamps?
Yes. EXTRACT(MONTH FROM some_timestamp) works the same way, and you can also extract HOUR and MINUTE.
Why does EXTRACT return a decimal in Postgres?
Since Postgres 14, EXTRACT returns a numeric. The value is still a whole number like 3. Cast it with ::int if a tool complains.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Revenue by month” →