Oracle LAST_DAY function

Get the date of the last day of the month in Oracle

The LAST_DAY() function returns the last day of the month that contains a date. The return type is always DATE, regardless of the datatype of date.

Syntax:

LAST_DAY(date)

Parameters:

Return Type:
The return type is always DATE.

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Pictorial Presentation

Examples: Oracle LAST_DAY() function

The following statement determines how many days are left in the current month.

SQL> SELECT SYSDATE,LAST_DAY(SYSDATE) "Last",
  2  LAST_DAY(SYSDATE) - SYSDATE "Days Left"
  3  FROM DUAL;

Sample Output:

SYSDATE   Last       Days Left
--------- --------- ----------
30-MAY-01 31-MAY-01        30  

The following example adds 6 months to the hire date of each employee to give an evaluation date :

SQL> SELECT last_name, hire_date,
 2    TO_CHAR(ADD_MONTHS(LAST_DAY(hire_date), 5)) "Evaluate Date"
 3    FROM employees;;

Sample Output:

LAST_NAME                 HIRE_DATE Evaluate Date
------------------------- --------- ------------------
King                      17-JUN-03 30-NOV-03
Kochhar                   21-SEP-05 28-FEB-06
De Haan                   13-JAN-01 30-JUN-01
Hunold                    03-JAN-06 30-JUN-06
Ernst                     21-MAY-07 31-OCT-07
Austin                    25-JUN-05 30-NOV-05
Pataballa                 05-FEB-06 31-JUL-06
Lorentz                   07-FEB-07 31-JUL-07
Greenberg                 17-AUG-02 31-JAN-03
Faviet                    16-AUG-02 31-JAN-03
Chen                      28-SEP-05 28-FEB-06
. . .

Previous: FROM_TZ
Next: LOCALTIMESTAMP