ORACLE LAST_DAY() Function By Practical Examples

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

Syntax

The following illustrates 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:

1) date

The date argument 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.

A) Get 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.

B) Calculate the number of days left of the current month

The following example returns the number of days left in the current month:

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

C) Return 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:

D) Get 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:

In this tutorial, you have learned how to use the Oracle LAST_DAY() function to get the last day of the month on a specified date.

Was this tutorial helpful?