How does the SQL version of YEAR, MONTH and TEXT dates work?

EXTRACT(YEAR FROM date) is YEAR(A2) and EXTRACT(MONTH FROM date) is MONTH(A2). TO_CHAR(date, 'YYYY-MM') formats the date as text, like TEXT with a format string. Grouping by these gives a monthly summary without a separate helper column.

Why can SQL give a different result from YEAR, MONTH and TEXT dates?

Excel format codes (yyyy-mm) are not the same as SQL ones ('YYYY-MM'); each database has its own format letters.

YEAR, MONTH and TEXT dates in SQL

YEAR, MONTH and TEXT pull parts out of a date or format it; in SQL EXTRACT and TO_CHAR do the same jobs.

The Excel version

=YEAR(A2), =MONTH(A2), =TEXT(A2, "yyyy-mm")

The SQL equivalent

This example assumes: invoices(id bigint, invoice_date date, amount numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT EXTRACT(YEAR FROM invoice_date)::int AS year,
       EXTRACT(MONTH FROM invoice_date)::int AS month,
       TO_CHAR(invoice_date, 'YYYY-MM') AS year_month,
       SUM(amount) AS total
FROM invoices
GROUP BY 1, 2, 3
ORDER BY 1, 2;

How it works

EXTRACT(YEAR FROM date) is YEAR(A2) and EXTRACT(MONTH FROM date) is MONTH(A2).
TO_CHAR(date, 'YYYY-MM') formats the date as text, like TEXT with a format string.
Grouping by these gives a monthly summary without a separate helper column.

Where the results differ from Excel

Excel format codes (yyyy-mm) are not the same as SQL ones ('YYYY-MM'); each database has its own format letters.
Grouping by month number alone mixes the same month of different years; include the year.
TO_CHAR returns text, which sorts alphabetically; YYYY-MM sorts correctly, but month names would not.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Total invoices by year and month.
How many invoices were raised in each month of 2026?