How to Get the Last Day of the Month in Oracle

Posted on by Ian

In Oracle Database we can use the LAST_DAY() function to return the last day of a given month. This can be the last day of the current month or the last day of month based on a specified date.

Example

SELECT LAST_DAY(date '2035-02-15')
FROM DUAL;

Result:

28-FEB-35

The last day of the month is defined by the session parameter NLS_CALENDAR. See How to Check the Values of the NLS Parameters to find out which calendar your session is using. Mine is using the Gregorian calendar.

Here’s what happens when we roll the date forward a year:

SELECT LAST_DAY(date '2036-02-15')
FROM DUAL;

Result:

29-FEB-36

The following year is a leap year, and so we get 29 days in February that year.

Note that the LAST_DAY() function returns a DATE value. This is true even if we pass a TIMESTAMP value:

SELECT LAST_DAY(TIMESTAMP '2036-07-15 15:30:45.12345')
FROM DUAL;

Result:

31-JUL-36

Current Month

This example gets the last day of the current month:

SELECT 
    SYSDATE,
    LAST_DAY(SYSDATE)
FROM DUAL;

Result:

SYSDATELAST_DAY(SYSDATE)
12-APR-2230-APR-22

In this case I used SYSDATE for the current date.

Next Month

We can use the ADD_MONTHS() function to add one or more months on to the current date:

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

Result:

SYSDATELAST_DAY(ADD_MONTHS(SYSDATE,1))
12-APR-2231-MAY-22

In this case, we got the last day of next month.

Last Month

We can add a negative value to get the last day of a previous month:

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

Result:

SYSDATELAST_DAY(ADD_MONTHS(SYSDATE,-1))
12-APR-2231-MAR-22