Oracle LAST_DAY function use cases | last day of financial year and calendar year

Here are some more Oracle SQL variations using LAST_DAY for different use cases:

---

1. Get Last Day of Any Given Year & Month

SELECT LAST_DAY(TO_DATE('01-06-2024', 'DD-MM-YYYY')) AS last_day_of_june FROM DUAL;

✅ This will return 30-JUN-2024.

---

2. Find Last Day of Previous, Current, and Next Month

SELECT 
    LAST_DAY(ADD_MONTHS(SYSDATE, -1)) AS last_day_previous_month,
    LAST_DAY(SYSDATE) AS last_day_current_month,
    LAST_DAY(ADD_MONTHS(SYSDATE, 1)) AS last_day_next_month
FROM DUAL;

✅ Dynamically fetches last days for previous, current, and next month.

---

3. Get Last Day of Financial Year (March 31st)

SELECT LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-03-01', 'YYYY-MM-DD')) AS last_day_fy FROM DUAL;

✅ If today is in 2024, this will return 31-MAR-2024.

---

4. Get Last Day of Calendar Year (December 31st)

SELECT LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE) || '-12-01', 'YYYY-MM-DD')) AS last_day_of_year FROM DUAL;

✅ Returns 31-DEC-2024 if run in 2024.

---

5. Find the Number of Days in a Given Month

SELECT LAST_DAY(SYSDATE) - TRUNC(SYSDATE, 'MM') + 1 AS days_in_month FROM DUAL;

✅ This calculates the number of days in the current month.

---

6. Get the Last Weekday of the Month

SELECT 
    CASE 
        WHEN TO_CHAR(LAST_DAY(SYSDATE), 'DY') IN ('SAT', 'SUN') 
        THEN NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'FRI') 
        ELSE LAST_DAY(SYSDATE) 
    END AS last_weekday_of_month
FROM DUAL;

✅ This finds the last weekday (Friday) of the current month.

---

7. Find the Last Sunday of the Month

SELECT NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'SUNDAY') AS last_sunday FROM DUAL;

✅ Adjust "SUNDAY" to any day you want.

Here are more advanced Oracle SQL queries using LAST_DAY for different scenarios:

---

8. Get Last Friday of the Month

SELECT 
    CASE 
        WHEN TO_CHAR(LAST_DAY(SYSDATE), 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') = 'FRI' 
        THEN LAST_DAY(SYSDATE)
        ELSE NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'FRIDAY')
    END AS last_friday
FROM DUAL;

✅ Returns the last Friday of the current month.

---

9. Get Last Working Day (Mon-Fri) of the Month

SELECT 
    CASE 
        WHEN TO_CHAR(LAST_DAY(SYSDATE), 'D') IN ('7', '1') -- Saturday or Sunday
        THEN NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'FRIDAY') 
        ELSE LAST_DAY(SYSDATE)
    END AS last_working_day
FROM DUAL;

✅ Adjusts to the last Monday-Friday workday.

---

10. Find the Last Monday of the Month

SELECT NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'MONDAY') AS last_monday FROM DUAL;

✅ Adjust "MONDAY" for other days.

---

11. Get the Last Date of Each Month in a Year

SELECT LAST_DAY(TO_DATE('01-' || LEVEL || '-2024', 'DD-MM-YYYY')) AS last_day 
FROM DUAL CONNECT BY LEVEL <= 12;

✅ Lists the last date of every month in 2024.

---

12. Get the Last Day of the Last 12 Months

SELECT LAST_DAY(ADD_MONTHS(SYSDATE, -LEVEL)) AS last_day 
FROM DUAL CONNECT BY LEVEL <= 12;

✅ Gets last days for the past 12 months.

---

13. Get the Last Business Day of the Month (Excluding Weekends & Holidays)

WITH holidays AS (
    SELECT TO_DATE('25-12-2024', 'DD-MM-YYYY') AS holiday FROM DUAL UNION ALL
    SELECT TO_DATE('01-01-2024', 'DD-MM-YYYY') FROM DUAL
)
SELECT 
    MAX(CASE 
            WHEN TO_CHAR(LAST_DAY(SYSDATE), 'D') IN ('7', '1') -- Sat/Sun
            THEN NEXT_DAY(LAST_DAY(SYSDATE) - 7, 'FRIDAY') 
            ELSE LAST_DAY(SYSDATE) 
        END) AS last_business_day
FROM DUAL 
WHERE LAST_DAY(SYSDATE) NOT IN (SELECT holiday FROM holidays);

✅ Excludes weekends and holidays.

---

14. Get Last Day of Previous Quarter

SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -1)) AS last_day_prev_quarter FROM DUAL;

✅ Returns last day of the previous quarter.

---

15. Get Last Day of Current Quarter

SELECT LAST_DAY(TRUNC(SYSDATE, 'Q') + INTERVAL '2' MONTH) AS last_day_current_quarter FROM DUAL;

✅ Returns last day of the current quarter.

---

16. Get Last Day of a Specific Quarter (e.g., Q3 2024)

SELECT LAST_DAY(TO_DATE('01-07-2024', 'DD-MM-YYYY') + INTERVAL '2' MONTH) AS last_day_q3_2024 FROM DUAL;

✅ Returns 30-SEP-2024 (last day of Q3 2024).

---

17. Get the Last Day of a Leap Year February

SELECT LAST_DAY(TO_DATE('01-02-2024', 'DD-MM-YYYY')) AS last_february_day FROM DUAL;

✅ Returns 29-FEB-2024 for leap years.

---

18. Get the Last Day of the Last 5 Years

SELECT LAST_DAY(TO_DATE('31-12-' || (EXTRACT(YEAR FROM SYSDATE) - LEVEL), 'DD-MM-YYYY')) AS last_day_of_year 
FROM DUAL CONNECT BY LEVEL <= 5;

✅ Returns last days of the last 5 years.

---

19. Get the Last Day of Next 5 Years

SELECT LAST_DAY(TO_DATE('31-12-' || (EXTRACT(YEAR FROM SYSDATE) + LEVEL), 'DD-MM-YYYY')) AS last_day_of_year 
FROM DUAL CONNECT BY LEVEL <= 5;

✅ Returns last days of the next 5 years.

---

20. Get the Last Day of a Dynamic Fiscal Year (April - March)

SELECT LAST_DAY(TO_DATE(EXTRACT(YEAR FROM ADD_MONTHS(SYSDATE, 9)) || '-03-01', 'YYYY-MM-DD')) AS last_day_fiscal_year FROM DUAL;

✅ Returns 31-MAR-2025 if today is in 2024.