Oracle PL/SQL Programming: A Developer's Workbook

Expert

Q:
19-17.	It’s a bit more complex than it sounds at first. You need to use NEXT_DAY to obtain the nearest earlier date and nearest later date. Then, perform date arithmetic to see how far away each is from the specified date. Finally, compare the “distances” and return the appropriate date. Here is one implementation: /* Filename on web page: nearday.sf */ CREATE OR REPLACE FUNCTION nearestday ( yourdate IN DATE, dayname IN VARCHAR2) RETURN DATE IS before_date DATE := NEXT_DAY (yourdate-7, dayname); after_date DATE := NEXT_DAY (yourdate, dayname); before_diff NUMBER; after_diff NUMBER; BEGIN before_diff := yourdate - before_date; after_diff := yourdate - after_date; IF before_diff < after_diff THEN RETURN before_date; ELSE RETURN after_date; END IF; END; /
Q:
19-18.	You can take one of two approaches: Compute the number of Saturdays and Sundays between the two dates and subtract that from the total. I’ll call this the “brute-force” method. Execute a loop from start date to end date and keep count, ignoring the weekend. I’ll call this the “smart” method. Here is a solution following the brute-force method: /* Filename on web page: bizbetwn2.sf */ CREATE OR REPLACE FUNCTION bizdays_betwn ( ld_date1 DATE, ld_date2 DATE) RETURN NUMBER AS ln_diff NUMBER; ln_bus_days NUMBER; ld_date DATE; ...