PLSQL | LAST_DAY Function

The PLSQL LAST_DAY function is used for returning the last day of the month based on a date value. The last day of the month is defined by the session parameter NLS_CALENDAR.

The LAST_DAY function accepts one parameter which is the date value used to calculate the last day of the month. The LAST_DAY function returns a value of the DATE datatype, regardless of the datatype of date.

Syntax:

LAST_DAY(date)

Parameters Used:

date – It is used to specify the date value used to calculate the last day of the month.

Return Value:
The LAST_DAY function in PLSQL returns a DATE type value.

Supported Versions of Oracle/PLSQL:

1. Oracle 12c
2. Oracle 11g
3. Oracle 10g
4. Oracle 9i
5. Oracle 8i

Example-1: Getting the last day of the current month.

SELECT
  LAST_DAY(SYSDATE)
FROM
  dual; 

Output:

31.10.19 

Example-2: Getting the last day of a specific month.

SELECT
 LAST_DAY(TO_DATE('2003/02/12', 'yyyy/mm/dd'))
FROM
  dual; 

Output:

Feb 28, 2003 

Example-3: Returning the number of days left of the current month.

SELECT
  LAST_DAY( SYSDATE ) - SYSDATE
FROM
  dual; 

Output:

2 

The system considers 29th October 2019 as the current date while the article was written. It may vary from user to user.

Example-4: Returning the last day of the previous month.

SELECT
   LAST_DAY(ADD_MONTHS(SYSDATE, -1 ))
FROM
  dual; 

Output:

30.09.19 

The system considers 29th October 2019 as the current date while the article was written. It may vary from user to user.

Example-5: Returning the last day of the next month.

SELECT
   LAST_DAY(ADD_MONTHS(SYSDATE, 1 ))
FROM
  dual; 

Output:

30.11.19 

The system considers 29th October 2019 as the current date while the article was written. It may vary from user to user.