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.
Best answer by Krister Liljedahl
View original
+6
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
+6
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 dualThis 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?
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
