Get nearest day : Utility Function « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Function

SQL>
SQL> CREATE OR REPLACE FUNCTION nearestday (yourdate IN DATE, dayname IN VARCHAR2)
  2     RETURN DATE
  3  IS
  4     before_date DATE := NEXT_DAY (yourdate-7, dayname);
  5     after_date DATE := NEXT_DAY (yourdate, dayname);
  6
  7     before_diff NUMBER;
  8     after_diff NUMBER;
  9  BEGIN
 10     before_diff := yourdate - before_date;
 11     after_diff := yourdate - after_date;
 12     IF before_diff < after_diff
 13     THEN
 14        RETURN before_date;
 15     ELSE
 16        RETURN after_date;
 17     END IF;
 18  END;
 19  /

Function created.

SQL>