LAST_DAY

Return the last day of the month that contains the given date.

Category: datetimeReturns: DATEDialect: Standard

Syntax

LAST_DAY(date)

Description

## Overview Returns the DATE of the last calendar day of the month that contains the input date. Respects actual month lengths (28, 29, 30, or 31 days) and handles leap years correctly for February. Use LAST_DAY for month-end billing cycles, fiscal-period closing dates, and as a building block for 'days remaining in the month' calculations. ## Behavior - Returns a DATE. - Returns NULL if the argument is NULL. - Always returns a date whose DAYOFMONTH is 28, 29, 30, or 31. - Correctly handles leap years: LAST_DAY('2024-02-15') returns 2024-02-29, LAST_DAY('2025-02-15') returns 2025-02-28. - Accepts DATE input; TIMESTAMP is cast to DATE. ## Compatibility - Matches the traditional LAST_DAY convention used widely in analytical SQL engines.

Parameters

Name	Type	Description
`date`		Specifies the input date. LAST_DAY returns the last day of the containing month for this date.

Examples

-- March 31 for any date in March
SELECT LAST_DAY(DATE '2025-03-15') AS month_end;

-- February in a non-leap year: February 28
SELECT LAST_DAY(DATE '2025-02-01') AS month_end;

-- February in a leap year: February 29
SELECT LAST_DAY(DATE '2024-02-01') AS month_end;

-- Days remaining in the current month
SELECT DATEDIFF(LAST_DAY(CURRENT_DATE()), CURRENT_DATE()) AS days_remaining;

-- Month-end closing date for each invoice
SELECT invoice_id, LAST_DAY(invoice_date) AS month_end
FROM billing.accounts.invoices;

-- Count invoices due on month-end
SELECT COUNT(*) AS month_end_invoices
FROM billing.accounts.invoices
WHERE due_date = LAST_DAY(invoice_date);

Pitfalls

LAST_DAY returns the end of the containing calendar month, not a fiscal month. Organizations with 4-4-5 or other non-calendar fiscal months need a custom calendar table.
LAST_DAY combined with ADD_MONTHS can lose information: ADD_MONTHS(LAST_DAY('2025-01-15'), 1) returns February 28 (end-of-month clamp), while LAST_DAY(ADD_MONTHS('2025-01-15', 1)) returns February 28 (because February 15 plus nothing special produces 2025-02-28 as the last day). The two nested forms happen to agree here but can diverge with day-preserving inputs.
LAST_DAY(DATE '2024-02-29') returns 2024-02-29. Applications that expect 'LAST_DAY always returns a date whose DAYOFMONTH >= input day' should note that equality is possible when the input is already the last day.