Skip to main content

We have a quick report where we’d like to add 2 business days (exclude the weekends) to the CustomerOrder DateEntered.

We usually do this but this includes weekends- is there a date function that skips Saturday and Sunday?

I’m familiar with work_time_calendar_api.get_work_days_between() but don’t see how that can do the above.

 

 

HI ,

 

Work_Time_Calendar_API.Get_Next_Work_Day will get the next working day accordingly to the calendar.

Work_Time_Calendar_API.Is_Working_Day will chek the date to see its a working day or not.


Cheers,


Thilini

Try this:

 

If the entered date + 2 is a Saturday then add 2

If the entered date + 2 is a Sunday then add 1

 

 

Hi ,


Work_Time_Calendar_API.Get_Next_Work_Day will get the next working day accordingly to the calendar.
Work_Time_Calendar_API.Is_Working_Day will check the date to see its a working day or not.


Cheers,
Thilini

The code below will give you dates excluding the weekend and hope this helps, 

WITH DATA AS
(SELECT to_date('05/01/2015', 'DD/MM/YYYY') date1,
to_date('31/05/2015', 'DD/MM/YYYY') date2
 FROM dual
 )
 SELECT date1+LEVEL-1 the_date,
 TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=ENGLISH') day
FROM DATA
WHERE TO_CHAR(date1+LEVEL-1, 'DY','NLS_DATE_LANGUAGE=ENGLISH')
 NOT IN ('SAT', 'SUN')
CONNECT BY LEVEL <= date2-date1+1;

IFS creates a neat sequencing counter for each calendar (I’m in the assumption you make use of calendars). So you will want to get the counter of the current day, add 2 days to that, and then retrieve the day of that . Example is below.

select ifsapp.Work_Time_Calendar_API.Get_Work_Day('*', ifsapp.Work_Time_Calendar_API.Get_Work_Day_Counter('*', trunc(sysdate)) + 2)
  from dual

This report won’t work on non-working days due to the fact that day not defined as a working day in the calendar, i.e. they day doesn’t correspond with a counter. You could use ifsapp.Work_Time_Calendar_API.Get_Nearest_Work_Day to fix that (or next work day, depending on the usage), if it is a problem.

I think what you are after is the function Work_Time_Calendar_API.Get_End_Date. The third parameter is duration_ (in number of days) and you can enter 2 there. So if your calendar is set up without weekend days, if you enter a date that is e.g. a Friday, with duration 2, it would return the date for Tuesday the next week.

A work calendar can also be set up with exceptions for public/bank holidays like Christmas Day etc and the above method will handle that as well.

Thx! That worked!

Any reason why the time didn’t get calculated/populated for Calc_Wanted_Dated?

 

Terms & ConditionsCookie settings