How does the SQL version of EOMONTH work?

DATE_TRUNC('month', ...) gives the first day of the month. Adding one month gives the first day of the next month, and subtracting one day gives the last day of this month. Adding two months in the same way gives EOMONTH(A2, 1), the end of next month.

Why can SQL give a different result from EOMONTH?

PostgreSQL has no EOMONTH function; MySQL has LAST_DAY(date) and SQL Server has EOMONTH(date), like Excel.

EOMONTH in SQL

EOMONTH returns the last day of a month, optionally some months ahead or behind; in SQL you truncate to the month, add a month and subtract a day.

The Excel version

=EOMONTH(A2, 0)

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 id,
       invoice_date,
       (DATE_TRUNC('month', invoice_date) + INTERVAL '1 month')::date - 1 AS month_end,
       (DATE_TRUNC('month', invoice_date) + INTERVAL '2 months')::date - 1 AS next_month_end
FROM invoices
ORDER BY id;

How it works

DATE_TRUNC('month', ...) gives the first day of the month.
Adding one month gives the first day of the next month, and subtracting one day gives the last day of this month.
Adding two months in the same way gives EOMONTH(A2, 1), the end of next month.

Where the results differ from Excel

PostgreSQL has no EOMONTH function; MySQL has LAST_DAY(date) and SQL Server has EOMONTH(date), like Excel.
Month arithmetic clamps day numbers (31 January plus one month is 28 or 29 February); truncating first avoids surprises.
The result type is a date, while Excel shows a serial number unless the cell is formatted as a date.

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.

Show each invoice with the end of its month.
Total invoices by month-end date.