Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 238 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K 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)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 437 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 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 '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 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
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 M-F and Sunday
select trunc(sysdate)+4,add_working_days(6,trunc(sysdate)+4) from dual;
Output : 19-sep2009 29-Sep-2009 -
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 M-F and Sunday
select trunc(sysdate)+4,add_working_days(6,trunc(sysdate)+4) from dual;
Output : 19-sep2009 29-Sep-2009 -
Hi, Niren,
[This thread|http://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('10-AUG-2009',10) FROM DUAL; NEXT_WORK --------- 20-AUG-09 SQL> select next_working_day('11-SEP-2009',1) from dual; NEXT_WORK --------- 14-SEP-09 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 10-AUG-2009 + 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, 10-AUG-2009 + 10 = 20-AUG-2009 which falls on Thursday.
11-SEP-2009 + 1 = 12-SEP-2009 which falls on Saturday. So, the next working day is Monday which is 14-Sep-2009.
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.