return Nth business date after or before start date (excluding Saturday and Sunday) using PL/SQL

GetNthBusinessDay.sql

-- return day of week (Mon=1, Tue=2, ... Sun=7)
FUNCTION WeekDay(weekdayName IN VARCHAR2) RETURN INTEGER AS
    weekDay INTEGER;
BEGIN 
    SELECT DECODE(weekdayName, 'Mon', 1, 'Tue', 2, 'Wed', 3, 'Thu', 4, 'Fri', 5, 'Sat', 6, 'Sun', 7) INTO weekDay FROM Dual;
    RETURN weekDay;
end WeekDay;
--End function return day of week

-- return Nth business date after or before start date (excluding Saturday and Sunday)
FUNCTION GetNthBusinessDay(startDate IN DATE, daysOffset IN NUMBER) RETURN DATE AS
    weekdayName VARCHAR2(10);
    startWeekday INTEGER;
    endWeekday INTEGER;
    weeksCount INTEGER;
    remainingDays INTEGER;
BEGIN
    IF startDate IS NULL THEN RETURN NULL; END IF;
    -- Each calendar week contains 5 business days (Mon to Fri)
    weeksCount := TRUNC(daysOffset / 5);
    remainingDays := MOD(daysOffset, 5);
    weekdayName := TO_CHAR(startDate, 'Dy', 'NLS_DATE_LANGUAGE=ENGLISH');
    startWeekday := WeekDay(weekdayName);
    endWeekday := startWeekday + remainingDays;
    IF daysOffset >= 0 THEN
        IF WeekDay('Sat') BETWEEN startWeekday AND endWeekday THEN
            remainingDays := remainingDays + 2; -- skip Sat and Sun
        ELSIF WeekDay('Sun') BETWEEN startWeekday AND endWeekday THEN
            remainingDays := remainingDays + 1; -- skip Sun
        END IF;
    ELSE
        -- remainingDays < 0 when daysOffset < 0, so it's possible that endWeekday < Monday
        IF (endWeekday < WeekDay('Mon')) THEN
            endWeekday := endWeekday + 7;
            startWeekday := startWeekday + 7;
        END IF;
        IF WeekDay('Sun') BETWEEN endWeekday AND startWeekday THEN
            remainingDays := remainingDays - 2; -- skip Sat and Sun
        ELSIF WeekDay('Sat') BETWEEN endWeekday AND startWeekday THEN
            remainingDays := remainingDays - 1; -- skip Sat
        END IF;
    END IF;
    RETURN startDate + (weeksCount * 7 + remainingDays);
end GetNthBusinessDay;