Oracle LAST_DAY Function: A Simple Guide

The LAST_DAY function in Oracle SQL is a very convenient datetime function that returns the date of the last day of the month for a given date.

It's an essential tool for any kind of financial, billing, or end-of-period reporting where you need to calculate month-end dates.

What is the LAST_DAY Function in Oracle?

The LAST_DAY(date) function takes a DATE value and returns a new DATE value representing the final day of that month.

• LAST_DAY('10-FEB-2024') returns '29-FEB-2024' (because 2024 is a leap year).
• LAST_DAY('10-SEP-2025') returns '30-SEP-2025'.
• LAST_DAY('10-OCT-2025') returns '31-OCT-2025'.

This is extremely useful for finding out how many days are left in a month or for setting billing dates to the end of the month.

LAST_DAY Function Syntax

The syntax for LAST_DAY is very simple:

LAST_DAY(date)

Let's break that down:

• date: The input date or DATE column. The function will find the last day of the month that this date belongs to. The return type is always DATE.

A Note on Viewing Results

To see the date clearly, you may want to set your session's date format. This command is helpful but not required.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

Oracle LAST_DAY Function Examples

Here are two practical examples of how to use LAST_DAY.

Example 1: Finding Days Left in the Month with LAST_DAY

This is a classic use case. You can use LAST_DAY with SYSDATE (the current date) to calculate how many days are left in the current month.

Query:

SELECT 
  SYSDATE AS "Today",
  LAST_DAY(SYSDATE) AS "Last_Day_of_Month",
  LAST_DAY(SYSDATE) - TRUNC(SYSDATE) AS "Days_Left"
FROM DUAL;

Result: (Assuming today is '20-NOV-2025')

Today       Last_Day_of_Month  Days_Left
----------- ----------------- ----------
20-NOV-2025 30-NOV-2025                 10

(Note: We use TRUNC(SYSDATE) to remove the time component, ensuring an accurate integer for the "Days Left".)

Example 2: Using LAST_DAY with a Table Column

Imagine you have a employees table and you want to find the end-of-month date for everyone's hire_date.

Query:

SELECT 
  last_name,
  hire_date,
  LAST_DAY(hire_date) AS "End_of_Hire_Month"
FROM employees
WHERE ROWNUM <= 5; -- Just show 5 rows for this example

Result: (The results will depend on your employee data)

LAST_NAME                 HIRE_DATE   End_of_Hire_Month
------------------------- ----------- -----------------
King                      17-JUN-03   30-JUN-03
Kochhar                   21-SEP-05   30-SEP-05
De Haan                   13-JAN-01   31-JAN-01
Hunold                    03-JAN-06   31-JAN-06
Ernst                     21-MAY-07   31-MAY-07