Skip to Main Content
Oracle PL/SQL Programming: A Developer's Workbook
book

Oracle PL/SQL Programming: A Developer's Workbook

by Steven Feuerstein, Andrew Odewahn
May 2000
Intermediate to advanced content levelIntermediate to advanced
594 pages
11h 32m
English
O'Reilly Media, Inc.
Content preview from 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; ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Database 12c PL/SQL Programming

Oracle Database 12c PL/SQL Programming

Michael McLaughlin
Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

Arup Nanda, Steven Feuerstein
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 9781449324070Supplemental ContentErrata Page