You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Function to return a date 'X' number of business days forward

1 reply [Last post]
Guest's picture
Guest

Can anyone tell me what's wrong with this function please?

/*Function to return a date a specified number business days from an input date */
Function getBusinessDays(&inputDate, &numDays) Returns date
/* Date variable to hold future date to be returned */
Local date &futureDate;

/* SQL Statement to return future date */
SQLExec("SELECT %dateout(MAX(wkngDate)) FROM (SELECT wkngDate, busday FROM (SELECT (TO_DATE(:1,'DD-MON-YYYY') + rownum + 1) wkngDate,(to_char(TO_DATE(:1,'DD-MON-YYYY') + rownum + 1, 'DY')) busday FROM all_objects ORDER BY 1) NO_WKEND WHERE to_char(NO_WKEND.wkngDate, 'DY') NOT IN ('SAT', 'SUN') AND (NO_WKEND.wkngDate NOT IN (SELECT to_char(holiday) FROM ps_holiday_date WHERE HOLIDAY_SCHEDULE ='REG7.5')) ORDER BY 1 ASC) NO_WKEND_HOLID WHERE rownum < :2", &inputDate, &numDays, &futureDate);

Return &futureDate;

End-Function;

On a peoplesoft page when someone enters a date in a certain field I need to automatically set several dates a specified number of business days in the future. So I feed the function the entered date and a number of business days. I've tried several different methods but everything so far has cause errors. I'm relatively new to peoplecode so it's entirely possible that the obvious has escaped me. In short ....I need help.

Lepa's picture
User offline. Last seen 3 days 10 hours ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Function to return a date 'X' number of business days ...

Why not sue the delivered AddToDate(date, num_years, num_months, num_days) function to add specific # of days to the date being entered on the page.

For example, if you want to add 5 days to that date then AddToDate (&dateOnPage, 0, 0, 5);

You can then use the Weekday function Weekday(dt) to determine if that future date is on Sunday or Saturday. The Weekday function returns a Number value representing the day of the week. 1 is Sunday, 7 is Saturday. From there you can either add or subtract a day or two to get the future day to be on Friday or Monday since i'm thinking you don't want the future day to be on a weekend.

As for holiday days, I think you are already trying to use ps_holiday_date to figure out if it is a holiday day, and so if it is, you can again add or subtract a day to get your non-holiday date.

Hope this helps and good luck.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog