Last Day of the Month and Next Day Function

Oracle provides a built-in function to get the last day of a month. The function is LAST_DAY,and it’s called as follows:

LAST_DAY (date)

The syntax element is:

date

Specifies a DATE value, or a string with a date in the default date format.

LAST_DAY returns the last day of the month containing the input date. For example, to find the last date of the current month, we can use the following SQL statement:

SELECT LAST_DAY(SYSDATE) "Next Payment Date" FROM DUAL;

Next Day

Oracle provides a built-in function to get the date of the next occurrence of a specified day of the week. The function is NEXT_DAY, and it’s called as follows:

NEXT_DAY (date, string) The syntax elements are:

date

Specifies a DATE value, or a string with a date in the default date format. string Specifies the name of a weekday.

To find the date of the next Friday, we can use the following SQL statement:

SELECT NEXT_DAY(SYSDATE, 'Friday') "Vacation Start Date" FROM DUAL;