LAST_DAY

The LAST_DAY function in Oracle is used to return the last day of the month for a given date. This is a useful function when you need to calculate the end of a month, whether for financial reporting, scheduling, or time period calculations.

 

Syntax:

LAST_DAY(date)

• date: The input date (of type DATE, TIMESTAMP, or TIMESTAMP WITH TIME ZONE) for which you want to find the last day of the month.

Return Type:

• The function returns a DATE value representing the last day of the month of the given date. The time component of the DATE or TIMESTAMP is set to 23:59:59 (just before midnight) by default.

 

Key Features of LAST_DAY:

1.     Returns the Last Day of the Month:

• The primary function of LAST_DAY is to return the last day of the month corresponding to the provided date.

Example:

SELECT LAST_DAY(DATE '2025-02-15') FROM dual;

-- Result: 2025-02-28 (The last day of February 2025)

2.     Works with DATE and TIMESTAMP Data Types:

• LAST_DAY works with both DATE and TIMESTAMP types. If a TIMESTAMP value is provided, the returned date will be set to 23:59:59, which is the last moment of the month.

Example:

SELECT LAST_DAY(TIMESTAMP '2025-03-05 14:30:00') FROM dual;

-- Result: 2025-03-31 23:59:59 (The last day of March 2025)

3.     Time Component of DATE and TIMESTAMP:

• When using DATE, the time part will default to 23:59:59 of the last day of the month.
• When using TIMESTAMP, the time component will also be set to 23:59:59, as the function assumes the last moment of the day.

Example with TIMESTAMP:

SELECT LAST_DAY(TIMESTAMP '2025-02-10 08:00:00') FROM dual;

-- Result: 2025-02-28 23:59:59 (The last day of February with time set to 23:59:59)

4.     Handles Leap Years Automatically:

• LAST_DAY automatically handles leap years. For instance, February in a leap year will return February 29 as the last day.

Example:

SELECT LAST_DAY(DATE '2024-02-15') FROM dual;

-- Result: 2024-02-29 (February 2024 is a leap year)

5.     No Time Zone Adjustment:

• If you use TIMESTAMP WITH TIME ZONE, the function will ignore the time zone and return the last day of the month, with the time set to 23:59:59 in the session's time zone.

 

Examples of LAST_DAY Usage:

1. Finding the Last Day of a Month:

• Get the last day of the month for 2025-05-15:

SELECT LAST_DAY(DATE '2025-05-15') FROM dual;

-- Result: 2025-05-31 (The last day of May 2025)

2. Finding the Last Day of the Current Month:

• Use SYSDATE to find the last day of the current month:

SELECT LAST_DAY(SYSDATE) FROM dual;

-- Result: (The last day of the current month, depending on the system date)

3. Finding the Last Day of a Specific Month with TIMESTAMP:

• Get the last day of the month for a TIMESTAMP value:

SELECT LAST_DAY(TIMESTAMP '2025-07-15 12:00:00') FROM dual;

-- Result: 2025-07-31 23:59:59 (The last day of July 2025 with time set to 23:59:59)

4. Handling Leap Year:

• Find the last day of February in a leap year (2024):

SELECT LAST_DAY(DATE '2024-02-01') FROM dual;

-- Result: 2024-02-29 (February 2024 has 29 days)

5. Finding the Last Day of a Quarter:

• If you want to calculate the last day of a quarter, you can use the LAST_DAY function. For example, for the first quarter of the year:

SELECT LAST_DAY(DATE '2025-03-15') FROM dual;

-- Result: 2025-03-31 (The last day of March, which is the end of Q1)

 

Important Considerations:

1.     Month Length Variations:

• The LAST_DAY function takes into account the variable number of days in each month. For example, February will return 28 or 29 days depending on whether it's a leap year.

2.     Time Zone Handling:

• If you pass a TIMESTAMP WITH TIME ZONE as an argument, Oracle will ignore the time zone part and return the date portion in the session's time zone.

3.     Use in Date Arithmetic:

• The LAST_DAY function is often used for date arithmetic and calculations, such as determining the end of a month, quarter, or fiscal year.

4.     Use in Business Logic:

• LAST_DAY is extremely useful in financial systems, where you need to calculate the last day of the month or determine month-end or quarter-end dates for reporting.

5.     Performance:

• The LAST_DAY function is highly optimized for calculating the last day of a month, and you can use it with large datasets without performance issues.

 

Common Use Cases:

1.     Financial Reporting:

• Used to calculate the last day of the month for generating financial statements or monthly reports.

2.     End of Period Calculations:

• Useful for applications that need to determine the last day of a quarter, fiscal year, or other time periods.

3.     Date Ranges:

• Can be used to calculate the end date for a date range, especially when the range ends at the end of the month.

Example:

SELECT LAST_DAY(DATE '2025-04-15') - 30 FROM dual;

-- Result: 2025-03-16 (30 days before the last day of April 2025)

4.     Business and Work Week Schedules:

• To find the last business day of the month, you can combine LAST_DAY with other date functions or business rules.

 

Summary:

The LAST_DAY function in Oracle is a powerful tool for determining the last day of the month for a given date. It works with DATE and TIMESTAMP types and handles leap years and month-length variations automatically. This function is widely used in business logic, financial systems, and reporting to calculate month-end dates, quarter-end dates, or the last day of specific time periods. By setting the time component to 23:59:59, it provides a precise representation of the last moment of the month.