Mega Code Archive
Get the next business day
<![CDATA[
SQL> create or replace function next_business_day (p_days in number)
2 return varchar
3 is
4 v_new_day varchar2(10);
5 v_new_date date;
6 begin
7 v_new_day := to_char((sysdate + p_days), 'fmDay');
8 v_new_date := sysdate + p_days ;
9
10 if v_new_day = 'Saturday' then
11 v_new_day := 'Monday' ;
12 v_new_date := v_new_date + 2 ;
13 elsif v_new_day = 'Sunday' then
14 v_new_day := 'Monday';
15 v_new_date := v_new_date + 1 ;
16 end if;
17
18 return(v_new_day || ' ' || to_char(v_new_date,'mm/dd/yyyy'));
19 end;
20 /
Function created.
SQL> show errors
No errors.
SQL>
SQL> -- Test the next_business_day function:
SQL> select next_business_day(2) from dual;
NEXT_BUSINESS_DAY(2)
--------------------------------------------------------------------------------
Monday 07/28/2008
SQL> select next_business_day(200) from dual;
NEXT_BUSINESS_DAY(200)
--------------------------------------------------------------------------------
Monday 02/09/2009
SQL>
////////////////////////////////////////////////
Unfortunately, your formula calculating number of business days between 2 dates has a defect. If you take the same business date for start_date and end_date it will give the difference of 1 business day, which is wrong.
I would propose the following correction:
SQL>
SQL> CREATE OR REPLACE FUNCTION bizdays_between (
2 start_date IN DATE, end_date IN DATE)
3 RETURN INTEGER
4 IS
5 v_sundays INTEGER :=
6 NEXT_DAY (end_date - 7, 'SUNDAY') -
7 NEXT_DAY (start_date - 1, 'SUNDAY');
8
9 v_saturdays INTEGER :=
10 NEXT_DAY (end_date - 7, 'SATURDAY') -
11 NEXT_DAY (start_date - 1, 'SATURDAY');
12 BEGIN
13 RETURN (
14 end_date -
15 start_date -
16 abs(sign(end_date - start_date))*((v_sundays + v_saturdays)/7 -
17 1)
18 );
19 END;
20 /
Sincerely,
Paul Filstein
paul.filstein at thehartford.com
]]>