How to Extract Year and Month from a Date
Last updated August 25, 2026 · By the SaturnSQL team
Use EXTRACT(YEAR FROM date) and EXTRACT(MONTH FROM date), which return numbers. TO_CHAR(date, 'MM') does the same but returns a zero-padded string.
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year,
EXTRACT(MONTH FROM hire_date) AS hire_month
FROM employees;EXTRACT vs. TO_CHAR
EXTRACT returns a NUMBER: EXTRACT(MONTH FROM hire_date) gives 8 for August, not '08'. TO_CHAR(hire_date, 'MM') returns the VARCHAR2 '08', zero-padded. The distinction matters for grouping and sorting: numeric months sort and compare naturally, while string months sort correctly too as long as they're zero-padded, but concatenating them into a year-month key ('YYYY' || '-' || 'MM') is safer done with TO_CHAR directly rather than casting EXTRACT's numbers back to text.
SELECT TO_CHAR(hire_date, 'YYYY-MM') AS year_month,
COUNT(*) AS hires
FROM employees
GROUP BY TO_CHAR(hire_date, 'YYYY-MM')
ORDER BY year_month;For arithmetic (e.g. filtering WHERE EXTRACT(YEAR FROM hire_date) = 2026) EXTRACT is the right tool because the comparison is numeric and needs no format model. Just be aware that EXTRACT on an indexed date column, like TRUNC, is a function call that a plain index on that column cannot support without a matching function-based index.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free