Use LAST_DAY to return the last day of a specified time unit - MaxCompute

Returns the last day of a specified time unit for a date. When called without date_part, it returns the last day of the month.

-- Returns 2025-10-31
SELECT LAST_DAY('2025-10-01');

Syntax

DATE|STRING LAST_DAY(DATE|DATETIME|TIMESTAMP|TIMESTAMP_NTZ|STRING <date>[, STRING <date_part>])

Parameters

date

Required. Accepted types: DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ, or STRING.

If the input is a STRING value, the date format must include yyyy-mm-dd.

date_part

Optional. A STRING constant that specifies the time unit. Default: month.

Time unit	Accepted values
Year	`year` or `yyyy`
ISO year	`isoyear` — uses the ISO 8601 week-numbering system. The year starts on the Monday of the first week containing the first Thursday of the Gregorian calendar year.
Quarter	`quarter` or `q`
Month	`month`, `mon`, or `mm`
Week	`week` — starts on Monday (equivalent to `week(monday)`). To use a custom start day, use `week(weekday)`, where `weekday` is one of: `SUNDAY`, `MONDAY`, `TUESDAY`, `WEDNESDAY`, `THURSDAY`, `FRIDAY`, `SATURDAY`.
ISO week	`isoweek` — uses ISO 8601 week boundaries. Starts on Monday.

Week variant behavior

The following query compares week, week(sunday), week(monday), and isoweek for the same input (2025-06-27, a Friday):

SELECT LAST_DAY('2025-06-27 11:40:00','week(sunday)') AS ld_week_sunday,
       LAST_DAY('2025-06-27 11:40:00','isoweek')      AS ld_isoweek,
       LAST_DAY('2025-06-27 11:40:00','week')         AS ld_week,
       LAST_DAY('2025-06-27 11:40:00','week(monday)') AS ld_week_monday;

Result:

+----------------+------------+------------+----------------+
| ld_week_sunday | ld_isoweek | ld_week    | ld_week_monday |
+----------------+------------+------------+----------------+
| 2025-06-28     | 2025-06-29 | 2025-06-29 | 2025-06-29     |
+----------------+------------+------------+----------------+

week(sunday) treats Saturday as the last day of the week, so the week containing 2025-06-27 (Friday) ends on 2025-06-28 (Saturday). week, week(monday), and isoweek all start on Monday, so the week ends on Sunday 2025-06-29.

Return value

The return type depends on whether date_part is provided:

With date_part: returns a DATE value.
Without date_part: returns a STRING in yyyy-mm-dd format.

If date is NULL, the function returns an error. If the value or format of date is invalid, the function returns NULL or an error.

Examples

Example 1: Basic date input

-- Returns 2025-03-31
SELECT LAST_DAY('2025-03-04');

-- Returns 2025-07-31
SELECT LAST_DAY('2025-07-04 11:40:00');

Example 2: NULL and invalid input

-- Returns an error (NULL input)
SELECT LAST_DAY(NULL,'isoyear');

-- Returns NULL (format does not include yyyy-mm-dd)
SELECT LAST_DAY('20250304');

Example 3: Specify a time unit

-- Returns 2025-03-31
SELECT LAST_DAY('2025-03-27 11:40:00','MONTH');

-- Returns 2025-12-31
SELECT LAST_DAY(DATETIME '2025-03-27 11:40:00','YEAR');

-- Returns 2025-03-31
SELECT LAST_DAY(DATETIME '2025-03-27 11:40:00','QUARTER');

Related functions

LAST_DAY is a date function. For other date calculation and conversion functions, see Date functions.