Function in param, start_date & number , return sums next working day

Shahid Ali

Dear User,
Simple Select can be used to get the desired result.

Solution is :
select decode(to_char((<your_date> + <days_to_add>),'FMDAY'),'SATURDAY',(((<your_date> + <days_to_add>) +2 ,'SUNDAY' ,(((<your_date> + <days_to_add> ) +1, (((<your_date> + <days_to_add> )) from dual;

Examples :
For sysdate as 16 sep 09

Case 1 : Sunday
SQL> select decode(to_char((sysdate + 4),'FMDAY'),'SATURDAY',(sysdate + 4 ) +2 ,'SUNDAY' ,(sysdate + 4 ) +1, (sysdate + 4 )) from dual;

DECODE(TO
---------
21-SEP-09

Case 2 : Saturday
SQL> select decode(to_char((sysdate + 3),'FMDAY'),'SATURDAY',(sysdate + 3 ) +2 ,'SUNDAY' ,(sysdate + 3 ) +1, (sysdate + 3 )) from dual;

DECODE(TO
---------
21-SEP-09


Case 2 : Week day
SQL> select decode(to_char((sysdate + 2 ),'FMDAY'),'SATURDAY',(sysdate + 2 ) +2 ,'SUNDAY' ,(sysdate + 2 ) +1, (sysdate + 2 )) from dual;

DECODE(TO
---------
18-SEP-09


Revert me back in case of any confusion .

Sven W.

user3021403 wrote:
Hi Friends,

I need a funtion which can take in parameters as start_date and number , sum them as new date and return new_start_date as workind day.
Suppose '15-SEP-2009' + 6 should return 23-SEP-2009 as new date.
Basically the sum of date and number should return next working day (excluding saturday,sunday).
e.g '15-SEP-2009' + 6 =21-SEP-2009 , but it includes saturday and sunday , so it should return 23-SEP-2009.

Thanks
Niren

I've written such a logic some years ago. I think you have already given some usable results. Just a few comments from my own experience. There are special scenarios you should think about, how you want to handle them.

1) You add a weekend as date and add 0. Should the next monday be returned or should it stay at the weekend day?
2) Is the number parameter the number of workdays added or the number of days? Like: What should the result be if you add 20 days, will all weekends be skipped? You already gave a clear definition, but it should be specifically mentioned that the number to add is not a number of working days.
3) At a later point you might want to include holidays too (from a holiday table, for example)
4) What about negative numbers? Can your function also calculate back in time? Do you want the friday in this case or the next monday?

722698

Hi Friends,

Thanks for the reply, i have developed the solution.
Sending the code for the benefit of others.

CREATE OR REPLACE FUNCTION NIREN_ADD_DAYS (
p_date DATE,
p_days NUMBER
)
RETURN DATE
IS

v_date DATE;
v_count NUMBER;

BEGIN

SELECT
DECODE(
RTRIM(TO_CHAR(p_date, 'DAY')),
'SATURDAY',
NEXT_DAY(p_date, 'SUNDAY'),
NEXT_DAY((p_date - 7), 'SUNDAY')
)
INTO
v_date
FROM
dual;

v_count := p_date - v_date;

IF (v_count = -1)
THEN

v_count := 0;

END IF;

v_count := v_count + p_days;

IF (v_count > 5)
THEN

v_count := v_count + (FLOOR(v_count / 5.1) * 2);

END IF;

RETURN (v_date + v_count);

END NIREN_ADD_DAYS;
/