All Products
Search

This Product

    MaxCompute:LAST_DAY

    Document Center

    MaxCompute:LAST_DAY

    Last Updated:Dec 10, 2025

    The LAST_DAY function returns the last day of a specified time unit, date_part, for an input date.

    Syntax

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

-- Standard example.
-- The return value is 2025-10-31.
SELECT LAST_DAY('2025-10-01');

    Parameters

    • date: Required. A value of the DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ, or STRING data type.

      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 for the calculation. If this parameter is omitted, the function returns the last day of the month by default. The following values are supported for this parameter:

      Time unit

      Value

      Year

      year or yyyy

      ISO year

      isoyear uses the year boundary from the ISO 8601 week-numbering system. An ISO year boundary is the Monday of the first week of the year. The first week is the one that contains the first Thursday of the Gregorian calendar year.

      Quarter

      quarter or q

      Month

      month or mon or mm

      Week

      • week: A week starts on Monday. This is equivalent to week(monday).

      • week(weekday): A week starts on the specified day of the week (weekday).

        Valid values: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, and SATURDAY.

      ISO week

      isoweek uses the week boundaries defined in ISO 8601. An ISO week starts on Monday.

    Return value

    The function's return type depends on whether the date_part argument is provided:

    • If you specify date_part , a DATE value is returned. If you do not specify date_part, a STRING value is returned in the yyyy-mm-dd format.

    • If the value of date is not a DATE, DATETIME, TIMESTAMP, TIMESTAMP_NTZ, or STRING data type, or if its format is invalid, the function returns NULL or an error.

    • If the value of date is NULL, the function returns an error.

    Examples

    • Example 1: Basic date input.

      -- The return value is 2025-03-31.
SELECT LAST_DAY('2025-03-04');

-- The return value is 2025-07-31.
SELECT LAST_DAY('2025-07-04 11:40:00');

    • Example 2: Pass a NULL value or an invalid string.

      -- An error is returned.
SELECT LAST_DAY(NULL,'isoyear');

-- The return value is NULL.
SELECT LAST_DAY('20250304');

    • Example 3: Specify a time unit, such as 'MONTH', 'YEAR', or 'QUARTER'.

      -- The return value is 2025-03-31.
SELECT LAST_DAY('2025-03-27 11:40:00','MONTH');

-- The return value is 2025-12-31.
SELECT LAST_DAY(DATETIME '2025-03-27 11:40:00','YEAR');

-- The return value is 2025-03-31.
SELECT LAST_DAY(DATETIME '2025-03-27 11:40:00','QUARTER');

    • Example 4: Compare the results of week and isoweek.

      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;
       
-- The following result is returned.
+----------------+------------+------------+----------------+
| ld_week_sunday | ld_isoweek | ld_week    | ld_week_monday | 
+----------------+------------+------------+----------------+
| 2025-06-28     | 2025-06-29 | 2025-06-29 | 2025-06-29     | 
+----------------+------------+------------+----------------+

    Related functions

    LAST_DAY is a date function. For more information about functions for date calculations and conversions, see Date functions.