Indian Financial Year (April–March) ending logic in Oracle SQL.

Indian Financial Year (April–March) ending logic in Oracle SQL.

---

🇮🇳 Financial Year (April–March)

Financial Year:

• Starts → 01-APR

• Ends → 31-MAR (next year)

---

✅ 1. Get Financial Year End Date for a Given Date

Example: '15-DEC-2025'

SELECT CASE
         WHEN TO_CHAR(TO_DATE('15-DEC-2025','DD-MON-YYYY'),'MM') >= '04'
         THEN TO_DATE('31-MAR-' || (TO_CHAR(TO_DATE('15-DEC-2025','DD-MON-YYYY'),'YYYY') + 1),'DD-MON-YYYY')
         ELSE TO_DATE('31-MAR-' || TO_CHAR(TO_DATE('15-DEC-2025','DD-MON-YYYY'),'YYYY'),'DD-MON-YYYY')
       END AS FIN_YEAR_END
FROM dual;

Result:

31-MAR-2026

---

✅ 2. Optimized & Cleaner Method (Recommended)

This is simpler and better for production code:

SELECT ADD_MONTHS(
         TRUNC(TO_DATE('15-DEC-2025','DD-MON-YYYY'),'YYYY') + INTERVAL '3' MONTH,
         12
       ) - 1 AS FIN_YEAR_END
FROM dual;

---

✅ 3. Dynamic Version (Using SYSDATE)

SELECT CASE
         WHEN EXTRACT(MONTH FROM SYSDATE) >= 4
         THEN TO_DATE('31-MAR-' || (EXTRACT(YEAR FROM SYSDATE) + 1),'DD-MON-YYYY')
         ELSE TO_DATE('31-MAR-' || EXTRACT(YEAR FROM SYSDATE),'DD-MON-YYYY')
       END AS FIN_YEAR_END
FROM dual;

---

✅ 4. Financial Year Start Date

SELECT ADD_MONTHS(
         TRUNC(TO_DATE('15-DEC-2025','DD-MON-YYYY'),'YYYY'),
         3
       ) AS FIN_YEAR_START
FROM dual;

---

✅ 5. Financial Year Label (Useful for SLA Reports)

SELECT CASE
         WHEN EXTRACT(MONTH FROM SYSDATE) >= 4
         THEN EXTRACT(YEAR FROM SYSDATE) || '-' || (EXTRACT(YEAR FROM SYSDATE)+1)
         ELSE (EXTRACT(YEAR FROM SYSDATE)-1) || '-' || EXTRACT(YEAR FROM SYSDATE)
       END AS FIN_YEAR
FROM dual;

Example Output:

2025-2026

---

🎯 Practical Use in SLA / Revenue Reports

You can filter data like this:

WHERE fault_date BETWEEN fin_year_start AND fin_year_end

or directly:

WHERE fault_date BETWEEN
      ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),3)
  AND ADD_MONTHS(TRUNC(SYSDATE,'YYYY'),15)-1

---

Here is a combined reusable package for Indian Financial Year (April–March), suitable for SLA / Revenue / MTTR reporting.

This is optimized, clean, and production-ready.

---

✅ Package Specification

CREATE OR REPLACE PACKAGE pkg_fin_year_util AS

    -- Returns Financial Year Start Date (01-APR-YYYY)
    FUNCTION get_fin_year_start (p_date DATE)
        RETURN DATE;

    -- Returns Financial Year End Date (31-MAR-YYYY)
    FUNCTION get_fin_year_end (p_date DATE)
        RETURN DATE;

    -- Returns Financial Year Label (e.g. 2025-26)
    FUNCTION get_fin_year_label (p_date DATE)
        RETURN VARCHAR2;

    -- Returns Financial Quarter (Q1–Q4, Apr–Mar basis)
    FUNCTION get_fin_quarter (p_date DATE)
        RETURN VARCHAR2;

END pkg_fin_year_util;
/

---

✅ Package Body

CREATE OR REPLACE PACKAGE BODY pkg_fin_year_util AS

    ---------------------------------------------------------
    -- Financial Year Start (01-APR-YYYY)
    ---------------------------------------------------------
    FUNCTION get_fin_year_start (p_date DATE)
        RETURN DATE
    IS
    BEGIN
        RETURN ADD_MONTHS(
                   TRUNC(p_date, 'YYYY'),
                   CASE
                       WHEN EXTRACT(MONTH FROM p_date) >= 4 THEN 3
                       ELSE -9
                   END
               );
    END get_fin_year_start;


    ---------------------------------------------------------
    -- Financial Year End (31-MAR-YYYY)
    ---------------------------------------------------------
    FUNCTION get_fin_year_end (p_date DATE)
        RETURN DATE
    IS
    BEGIN
        RETURN ADD_MONTHS(
                   TRUNC(p_date, 'YYYY'),
                   CASE
                       WHEN EXTRACT(MONTH FROM p_date) >= 4 THEN 15
                       ELSE 3
                   END
               ) - 1;
    END get_fin_year_end;


    ---------------------------------------------------------
    -- Financial Year Label (e.g. 2025-26)
    ---------------------------------------------------------
    FUNCTION get_fin_year_label (p_date DATE)
        RETURN VARCHAR2
    IS
        v_start_year NUMBER;
        v_end_year   NUMBER;
    BEGIN
        IF EXTRACT(MONTH FROM p_date) >= 4 THEN
            v_start_year := EXTRACT(YEAR FROM p_date);
            v_end_year   := v_start_year + 1;
        ELSE
            v_end_year   := EXTRACT(YEAR FROM p_date);
            v_start_year := v_end_year - 1;
        END IF;

        RETURN v_start_year || '-' || SUBSTR(v_end_year, 3, 2);
    END get_fin_year_label;


    ---------------------------------------------------------
    -- Financial Quarter (Apr–Mar)
    ---------------------------------------------------------
    FUNCTION get_fin_quarter (p_date DATE)
        RETURN VARCHAR2
    IS
        v_month NUMBER := EXTRACT(MONTH FROM p_date);
    BEGIN
        RETURN CASE
                   WHEN v_month BETWEEN 4 AND 6  THEN 'Q1'
                   WHEN v_month BETWEEN 7 AND 9  THEN 'Q2'
                   WHEN v_month BETWEEN 10 AND 12 THEN 'Q3'
                   ELSE 'Q4'
               END;
    END get_fin_quarter;

END pkg_fin_year_util;
/

---

✅ Example Usage

SELECT pkg_fin_year_util.get_fin_year_start(DATE '2025-12-15') AS FY_START,
       pkg_fin_year_util.get_fin_year_end(DATE '2025-12-15')   AS FY_END,
       pkg_fin_year_util.get_fin_year_label(DATE '2025-12-15') AS FY_LABEL,
       pkg_fin_year_util.get_fin_quarter(DATE '2025-12-15')    AS FY_QTR
FROM dual;

Output:

01-APR-2025
31-MAR-2026
2025-26
Q3

---

🚀 SLA / Revenue Example (Index Friendly Filtering)

⚠ Instead of:

WHERE pkg_fin_year_util.get_fin_year_label(fault_date) = '2025-26'

Use:

WHERE fault_date BETWEEN
      pkg_fin_year_util.get_fin_year_start(DATE '2025-12-15')
  AND pkg_fin_year_util.get_fin_year_end(DATE '2025-12-15')

This keeps index usage intact for large FMS / SLA tables.

---