O'Reilly logo

Oracle PL/SQL Programming: A Developer's Workbook by Andrew Odewahn, Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required