In Oracle Database, the LAST_DAY() function returns the last day of the month that contains the supplied date.

It accepts one argument, which is the date for which you want to find the last day of the month.

Syntax

The syntax goes like this:

LAST_DAY(date)

Where date is the date expression for which you want to find the last day of the month.

Example

Here’s an example:

SELECT LAST_DAY(DATE '2030-02-01')
FROM DUAL;

Result:

28-FEB-30

In this case, we’re using a date in February. As it turns out, February has 28 days in that year.

Here’s what happens if we increment the date to the next leap year:

SELECT LAST_DAY(DATE '2032-02-01')
FROM DUAL;

Result:

29-FEB-32

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.

Also, the date is returned in the format that’s specified for your session. In this example, my session is using the default format for when the NLS_TERRITORY parameter is set to AMERICA. You can always change your session’s datetime formats if you so wish.

Other Datetime Values

It also works with other datetime values, such as TIMESTAMP, etc:

SELECT LAST_DAY(TIMESTAMP '2030-02-01 10:30:45.34567')
FROM DUAL;

Result:

28-FEB-30

However, the return type is always DATE, regardless of the data type of the argument.

Out of Range Dates

Zero dates and other out of range dates result in an error.

Example:

SELECT LAST_DAY(DATE '0000-00-00')
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LAST_DAY(DATE '0000-00-00')
FROM DUAL
Error at Command Line : 1 Column : 22
Error report -
SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 -  "(full) year must be between -4713 and +9999, and not be 0"
*Cause:    Illegal year entered
*Action:   Input year in the specified range

As the error message states, the (full) year must be between -4713 and +9999, and not be 0.

Current Date

We can pass SYSDATE as the datetime argument to use the current date:

SELECT 
    SYSDATE,
    LAST_DAY(SYSDATE)
FROM DUAL;

Result:

     SYSDATE    LAST_DAY(SYSDATE) 
____________ ____________________ 
12-AUG-21    31-AUG-21           

Missing Argument

Calling LAST_DAY() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT LAST_DAY()
FROM DUAL;

Result:

Error starting at line : 1 in command -
SELECT LAST_DAY()
FROM DUAL
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00909: invalid number of arguments
00909. 00000 -  "invalid number of arguments"
*Cause:    
*Action: