Workday

Workday is a PL/SQL function that simulates the Excel workday() function. It returns the date that is a number of working days before or after the starting date. Working days exclude weekends. This function can be used to calculate invoice due dates, expected delivery times, or the number of days of work performed.

CREATE OR REPLACE FUNCTION workday(start_date DATE, days NUMBER)
  RETURN DATE
IS
  i   NUMBER := 0;
  tot NUMBER := 0;
BEGIN
  IF days = 0 THEN
     RETURN start_date;
  END IF;

  WHILE true LOOP
    IF days > 0 THEN
       i := i + 1;
    ELSE
       i := i - 1;
    END IF;

    IF TO_CHAR(start_date + i, 'FMDAY') NOT IN ('SATURDAY', 'SUNDAY') THEN
       tot := tot + 1;
    END IF;

    IF abs(tot) = abs(days) THEN
       exit;
    END IF;
  END LOOP;

  RETURN start_date + i;
END workday;
/
show errors