CCCP_Vova

-- This function takes in a start date and x number of working days.
-- The number of working days can be positive or negative
-- It returns the date that is x working days from the start date.
-- written by uber_spaced.  I spend time on freenode; usually in #perl and or #oracle

CREATE OR REPLACE FUNCTION GET_DT_BUSINESS_DAYS_FROM_DT(date_in IN DATE, num_days IN NUMBER)
	RETURN DATE
	AS
		direction NUMBER := 0;
		remainder NUMBER := 0;
		day_num CHAR;
		
		start_date DATE;
		end_date DATE;
		BEGIN
		start_date := date_in;
		remainder := ABS(MOD(num_days, 5));

		direction := -1;
		IF num_days > 0 THEN direction := 1; END IF;

		-- Make sure start date is not on a weekend
		day_num := TO_CHAR(start_date, 'D');
		WHILE  (day_num = '1') OR (day_num = '7')
		LOOP
			start_date := start_date + direction;
			day_num := TO_CHAR(start_date, 'D');
		END LOOP;

		end_date := start_date + (num_days - (remainder*direction)) / 5 * 7;

		WHILE remainder > 0
		LOOP
			end_date := end_date + direction;
			remainder := remainder -1;

			-- Move off of weekends.
			day_num := TO_CHAR(end_date, 'D');
			WHILE  (day_num = '1') OR (day_num = '7')
			LOOP
				
				end_date := end_date + direction;
				day_num := TO_CHAR(end_date, 'D');
			END LOOP;
		END LOOP;

		RETURN end_date;
	END GET_DT_BUSINESS_DAYS_FROM_DT;
/