LAST_DAY_OF()

All functions > DATE AND TIME > LAST_DAY_OF()

Returns the last calendar day of the period containing the given date or timestamp, as midnight on that day.

Signatures

Returns: Midnight TIMESTAMP on the last day of the period

LAST_DAY_OF(date: DATE | TIMESTAMP, unit: VARCHAR) → TIMESTAMP

Notes

• unit must be one of: WEEK, MONTH, QUARTER, HALF, YEAR (recognized case-insensitively after trimming whitespace)
• Week uses the same week boundaries as DATE_TRUNC(..., 'week') (Monday–Sunday)
• Half means January–June vs July–December (last day is June 30 or December 31)
• Non-literal unit values are evaluated at runtime; unknown values surface as an execution error
• Returns NULL if date or unit is NULL

Examples

SELECT
    f1 := LAST_DAY_OF(TIMESTAMP '2024-03-20 15:00:00', 'MONTH'), -- End of March
    f2 := LAST_DAY_OF(TIMESTAMP '2024-03-20 12:00:00', 'WEEK'), -- ISO week containing 20 Mar 2024 ends Sunday
    f3 := LAST_DAY_OF(TIMESTAMP '2024-02-10 00:00:00', 'QUARTER'), -- End of Q1
    f4 := LAST_DAY_OF(TIMESTAMP '2024-05-01 00:00:00', 'HALF'), -- End of first half-year
    f5 := LAST_DAY_OF(TIMESTAMP '2024-09-01 00:00:00', 'HALF'), -- End of second half-year
    f6 := LAST_DAY_OF(TIMESTAMP '2024-07-04 00:00:00', 'YEAR') -- End of calendar year
;