Categories:

Date & Time Functions

LAST_DAY

Returns the last day of the specified date part for a date or timestamp. Commonly used to return the last day of the month for a date or timestamp.

Syntax

LAST_DAY( <date_or_time_expr> [ , <date_part> ] )

Usage Notes

  • date_or_time_expr (Required) must be a date or timestamp expression.

  • date_part (Optional) is the date part for which the last day is returned. Possible values are year, quarter, month, or week (or any of their supported variations). For details, see Supported Date and Time Parts.

    The default is month.

  • When date_part is week (or any of its variations), the output is controlled by the WEEK_START session parameter. For more details, including examples, see Calendar Weeks and Weekdays.

  • The return value is always a date, even if date_or_time_expr is a timestamp.

Examples

Return the last day of the month for the specified date (from a timestamp):

SELECT TO_DATE('2015-05-08T23:39:20.123-07:00') AS "DATE",
       LAST_DAY("DATE") AS "LAST DAY OF MONTH";
+------------+-------------------+
| DATE       | LAST DAY OF MONTH |
|------------+-------------------|
| 2015-05-08 | 2015-05-31        |
+------------+-------------------+

Return the last day of the year for the specified date (from a timestamp):

SELECT TO_DATE('2015-05-08T23:39:20.123-07:00') AS "DATE",
       LAST_DAY("DATE", 'year') AS "LAST DAY OF YEAR";
+------------+------------------+
| DATE       | LAST DAY OF YEAR |
|------------+------------------|
| 2015-05-08 | 2015-12-31       |
+------------+------------------+