Discussions
Categories
 196.8K All Categories
 2.2K Data
 235 Big Data Appliance
 1.9K Data Science
 449.9K Databases
 221.6K General Database Discussions
 3.8K Java and JavaScript in the Database
 31 Multilingual Engine
 549 MySQL Community Space
 478 NoSQL Database
 7.9K Oracle Database Express Edition (XE)
 3K ORDS, SODA & JSON in the Database
 532 SQLcl
 4K SQL Developer Data Modeler
 186.9K SQL & PL/SQL
 21.3K SQL Developer
 295.5K Development
 17 Developer Projects
 138 Programming Languages
 292.1K Development Tools
 104 DevOps
 3.1K QA/Testing
 645.9K Java
 28 Java Learning Subscription
 37K Database Connectivity
 154 Java Community Process
 105 Java 25
 22.1K Java APIs
 138.1K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 17 Java Essentials
 158 Java 8 Questions
 85.9K Java Programming
 79 Java Puzzle Ball
 65.1K New To Java
 1.7K Training / Learning / Certification
 13.8K Java HotSpot Virtual Machine
 94.2K Java SE
 13.8K Java Security
 203 Java User Groups
 24 JavaScript  Nashorn
 Programs
 401 LiveLabs
 37 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.6K Other Languages
 2.3K Chinese
 171 Deutsche Oracle Community
 1.1K Español
 1.9K Japanese
 230 Portuguese
Function in param, start_date & number , return sums next working day
722698
Member Posts: 7
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 '15SEP2009' + 6 should return 23SEP2009 as new date.
Basically the sum of date and number should return next working day (excluding saturday,sunday).
e.g '15SEP2009' + 6 =21SEP2009 , but it includes saturday and sunday , so it should return 23SEP2009.
Thanks
Niren
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 '15SEP2009' + 6 should return 23SEP2009 as new date.
Basically the sum of date and number should return next working day (excluding saturday,sunday).
e.g '15SEP2009' + 6 =21SEP2009 , but it includes saturday and sunday , so it should return 23SEP2009.
Thanks
Niren
Answers

create or replace function add_working_days(
p_days in number,
p_dt in date default trunc(sysdate)
)
return date
as
v_weeks number;
v_adj number;
begin
v_weeks := trunc(p_days/5);
if to_number(to_char(p_dt,'D')) + mod(p_days,5) >= 7 then
v_adj := 2;
else
v_adj := 0;
end if;
return p_dt + 7*v_weeks + v_adj+mod(p_days,5);
end add_working_days;
This is not working correct if the date is SATURDAY, but seems working MF and Sunday
select trunc(sysdate)+4,add_working_days(6,trunc(sysdate)+4) from dual;
Output : 19sep2009 29Sep2009 
create or replace function add_working_days(
p_days in number,
p_dt in date default trunc(sysdate)
)
return date
as
v_weeks number;
v_adj number;
begin
v_weeks := trunc(p_days/5);
if to_number(to_char(p_dt,'D')) + mod(p_days,5) >= 7 then
v_adj := 2;
else
v_adj := 0;
end if;
return p_dt + 7*v_weeks + v_adj+mod(p_days,5);
end add_working_days;
This is not working correct if the date is SATURDAY, but seems working MF and Sunday
select trunc(sysdate)+4,add_working_days(6,trunc(sysdate)+4) from dual;
Output : 19sep2009 29Sep2009 
Hi, Niren,
[This threadhttp://forums.oracle.com/forums/thread.jspa?messageID=3351822�] should give you some ideas. It does it without using PL/SQL, so it's actually a harder problem.
In PL/SQL, you could loop through the days, starting with the start_date parameter and decrementing a counter that is initialized to the other parameter, stopping when the counter reaches 0.
Or you could write a recursive function. 
Like this?
SQL> ed Wrote file afiedt.buf create or replace function next_working_day(start_date in date, add_days in number) return date is return_date date; begin return_date:=start_date+add_days; if (UPPER(to_char(return_date,'FMDAY'))='SATURDAY') then return_date:=return_date+2; elsif (UPPER(to_char(return_date,'FMDAY'))='SUNDAY') then return_date:=return_date+1; end if; return return_date; end; SQL> / Function created. SQL> select next_working_day('10AUG2009',10) FROM DUAL; NEXT_WORK  20AUG09 SQL> select next_working_day('11SEP2009',1) from dual; NEXT_WORK  14SEP09 SQL>
Arun
Tested
Posted after testing 
Hi Frant,
Please explain me with an example. If you have the exact function/Procedure than it can really help.
Thanks
Niren 
Hi, Niren,
The first respondent gave a better function for testing for work days (Monday through Friday). If there's a bug with Saturdays, I'm sure that can fixed. Also,that function depends on your NLS settings. If that's an issue for you, that can be fixed, too. The idea that there are 5 work days in any 7 consecutive days will cut down the execution time considerable.
A function like I suggested would be useful if you had to consider holidays as well as weekends.
Here's an untested example:CREATE OR REPLACE FUNCTION work_days_away ( in_start_date DATE , in_day_cnt NUMBER ) RETURN DATE DETERMINISTIC IS ( IF in_start_date  TRUNC (in_start_date, 'IW') >= 5 OR is_holiday (in_start_date) THEN RETURN work_days_away ( in_start_date + 1 , in_day_cnt ); ELSIF in_day_cnt >= 1 THEN RETURN work_days_away ( in_start_date + 1 , in_day_cnt  1 ); ELSE RETURN in_start_date; END IF; END work_days_away;
This use the date format 'IW', which does not depend on NLS settings, rather than 'D', which does. 
Hi Arun,
The code example where 10AUG2009 + 10 days should return 22 as per the need but your code is not considering the saturday and sunday falling on 15,16 Aug 2009.
Thanks
Niren 
Hi,
I was under the impression that, after adding the days, if the result falls on saturday / sunday, it should consider that and return the next working day i.e monday.
So, 10AUG2009 + 10 = 20AUG2009 which falls on Thursday.
11SEP2009 + 1 = 12SEP2009 which falls on Saturday. So, the next working day is Monday which is 14Sep2009.
Please let me know if my understanding about your requirement is not correct.
Arun 
Hi Arun,
Basically the need is that any number of days added to a date should return date excluding all saturdays and sundays falling in between.
It would be great if you could help me on this .
Thanks
Niren
+919873314561 
How about this?
create or replace function add_working_days( p_days in number, p_dt in date default trunc(sysdate) ) return date as v_weeks number; v_adj number; begin v_weeks := trunc(p_days/5); if to_number(to_char(p_dt,'D')) + mod(p_days,5) <= 6 then v_adj := 1; elsif to_number(to_char(p_dt,'D')) + mod(p_days,5) = 7 then v_adj := 2; else v_adj := 0; end if; return p_dt + 7*v_weeks + v_adj+mod(p_days,5); end add_working_days;
Arun
Untested
This discussion has been closed.