Forum Stats

  • 3,826,110 Users
  • 2,260,598 Discussions
  • 7,896,789 Comments

Discussions

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

2»

Answers

  • Shahid Ali
    Shahid Ali Member Posts: 57
    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.
    Sven W. Member Posts: 10,535 Gold Crown
    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
    722698 Member Posts: 7
    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;
    /
This discussion has been closed.