Oracle LAST_DAY Function

In oracle, we can use the LAST_DAY() function to get the date of the last day of the month for a given date.

In this tutorial, we will explore how to use the last_day() function to fetch the last day of the month from a specific value.

Introduction to Oracle LAST_DAY Function

This function enables us to extract the last day of the month from a given date value. The function syntax is as shown:

LAST_DAY(date)

The date argument defines the date for which you want to get the last day of the month. The value of this argument can be either a date literal, a date expression, or a table column with date values.

The function will return a DATE type denoting the input value’s last day of the month.

Example Function Usage

The following examples help you understand how the function behaves when presented with various input types.

Example 1 – Basic Function Usage

The example below demonstrates using the last_day() function to extract the last day of the month from a literal date.

select last_day(date '2022-12-12') as last_day from dual;

The function will then return the date for the current month. In this case, the last day of December is the 31^st.

Check the output of the code above:

2022-12-31

Example 2 – Using the LAST_DAY Function with a Date Expression

We can also provide an expression for the date parameter. In the example below, the date expression is a simple function that returns the current date from the host system.

select last_day(sysdate) as last_day from dual;

In this case, the function should return on the last day of the month based on the current system time.

Expected output:

2022-12-31 09:56:20

Example 3 – Date Calculations Using the Last_Day Function

We can also use the last_day function to determine how many days remain in the current month.

select last_day(sysdate) - sysdate as rem from dual;

This function works by simply subtracting the number of days from the current date to the last date of the month.

An example output is as shown:

Example 4 – Using the LAST_DAY() Function with Table Column

Suppose we have a table with employee information as demonstrated below:

select FIRST_NAME, EMAIL, HIRE_DATE from EMPLOYEES;

Result:

We can calculate how many days were remaining to the end of the month when every employee was hired, as shown in the query below:

select FIRST_NAME, EMAIL, last_day(HIRE_DATE) - HIRE_DATE as days_remaining
from EMPLOYEES;

Result:

Conclusion

This tutorial taught us how to use the LAST_DAY function in the Oracle database. This function allows us to get the date of the last day of the month from a given date. This function is combined with Oracle’s other date and time functions, such as ADD_MONTHS and NEXT_DAY, to provide more functionality.