ORACLE LAST_DAY() Function

The Oracle LAST_DAY() takes a DATE argument and returns the last day of the month of that date.

Syntax #

Here’s the syntax of the Oracle LAST_DAY() function:

LAST_DAY(date)Code language: SQL (Structured Query Language) (sql)

Arguments #

The LAST_DAY() function accepts one argument:

date is a DATE value or any expression that evaluates to a DATE value of which you want to get the last day of the month.

Return value #

The Oracle LAST_DAY() function always returns a DATE value that represents the last day of the month of that input date.

Examples #

Let’s look at the following examples of using the LAST_DAY() function.

Getting the last day of the current month #

The following example returns the last day of the current month:

SELECT
  LAST_DAY(SYSDATE)
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

In this example, the SYSDATE function returns the current system date which is used as an input date for the LAST_DAY() function.

Calculating the number of days left of the current month #

The following example uses the LAST_DAY function to return the number of days left in the current month:

SELECT
  LAST_DAY( SYSDATE ) - SYSDATE
FROM
  dual;
Code language: SQL (Structured Query Language) (sql)

Returning the last day of the last/next month #

The following example uses the ADD_MONTHS() function to add and subtract one month and return the last day of the last and the next month:

SELECT
  LAST_DAY(ADD_MONTHS(SYSDATE,-1 )) LAST_DAY_LAST_MONTH,
  LAST_DAY(ADD_MONTHS(SYSDATE,1 )) LAST_DAY_NEXT_MONTH
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Here is the result:

Getting the last day of February of the leap years #

The following example shows how to use the LAST_DAY() function to get the last day of the month February in both leap years and non-leap years:

SELECT
  LAST_DAY( DATE '2000-02-01') LAST_DAY_OF_FEB_2000,
  LAST_DAY( DATE '2016-02-01') LAST_DAY_OF_FEB_2016,
  LAST_DAY( DATE '2017-02-01') LAST_DAY_OF_FEB_2017
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

The following picture illustrates the output of the query:

Summary #

Use the Oracle LAST_DAY() function to get the last day of the month on a specified date.

Was this tutorial helpful?