Oracle LAST_DAY function

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

The Oracle LAST_DAY() function returns the last day of the month for a given date. It always returns a value of the DATE datatype, regardless of the input date type.

Uses of Oracle LAST_DAY() Function:

• Retrieving the last day of the month: Get the last day of the month for any given date.


• Calculating days remaining in the month: Determine how many days are left from a specific date until the month's end.


• Generating month-end reports: Facilitate reporting or queries based on the last day of a month.


• Assisting in date-based calculations: Use in combination with other date functions (like ADD_MONTHS()) for evaluations or future date calculations.


• Simplifying end-of-month evaluations: Automatically adjusts for different month lengths (28, 30, or 31 days) including leap years.

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