19-1.

Use the ADD_MONTHS function, as shown here:

date_plus_6 := ADD_MONTHS (my_date, 6);

19-2.

You want to use the SYSDATE and TO_CHAR functions, along with the appropriate format mask:

CREATE OR REPLACE PROCEDURE show_now
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (TO_CHAR
      (SYSDATE, 'Month DDth, YYYY HH24:MI:SS'));
END;
/

19-3.

The LAST_DAY function should do the trick:

SQL> EXEC DBMS_OUTPUT.PUT_LINE (LAST_DAY(SYSDATE))
31-MAR-99

19-4.

If you don’t want the time component possibly messing up your date-range computations, you can truncate the date as follows:

my_date := TRUNC (my_date);

The TRUNC function by default truncates the time component. You can use other format masks to specify different levels of truncation (day, month, week, year, etc.).

19-5.

Again, TRUNC is the way to go:

my_date := TRUNC (my_date, 'Q'),

19-6.

The MONTHS_BETWEEN function does most of the work, but you also have to truncate the fractional component:

month_count := TRUNC (MONTHS_BETWEEN (start_date, end_date));

19-7.

There is no ADD_YEARS function, so you have to do a bit of translation. Here is the code to write in a database trigger that makes sure your employees are at least 18 years old:

IF ADD_MONTHS (:NEW.hire_date, -1*18*12) < :NEW.date_of_birth
THEN
   DBMS_OUTPUT.PUT_LINE (
      'Too young to hire...in the US anyway.'),
END IF;

19-8.

The default time in Oracle is midnight: 12:00:00 A.M.

19-9.

The easiest way to do this is to use the LAST_DAY function:

CREATE OR REPLACE FUNCTION days_left RETURN NUMBER
IS
BEGIN
   RETURN (LAST_DAY (SYSDATE) - SYSDATE);
END;
/

19-10.

I would use a combination of TRUNC and NEXT_DAY, as shown here:

DECLARE
   first_monday DATE;
BEGIN
   first_monday := NEXT_DAY (
      TRUNC (SYSDATE, 'MONTH'), 'MONDAY'),
   DBMS_OUTPUT.PUT_LINE (first_monday);
END;
/

19-11.

We’re talking time-zone differences, so the NEW_TIME function should work perfectly well. London is on Greenwich Mean Time, while the folks in Chicago are on Central Standard Time. Here is a function that does the conversion:

/* Filename on web page: chitime.sf */
CREATE OR REPLACE FUNCTION chitime RETURN DATE
IS
BEGIN
   RETURN NEW_TIME (SYSDATE, 'GMT', 'CST'),
END;
/

19-12.

TRUNC to the rescue once again:

CREATE OR REPLACE FUNCTION chitime RETURN DATE
IS
BEGIN
   RETURN TRUNC (SYSDATE, 'MONTH'),
END;
/

19-13.

Well, first you have to truncate the current date/time back to the beginning of the century (which also sets the time component to midnight), then move forward 25 years, make sure you’re on the first day of that year, and move forward nine hours. Here goes:

DECLARE
   amaze_me DATE;
BEGIN
   amaze_me := TRUNC (
        ADD_MONTHS (TRUNC (SYSDATE, 'CC'), 25 * 12), 'MONTH') + 9 / 24;
   DBMS_OUTPUT.put_line (
      TO_CHAR (
         amaze_me,
         'MM/DD/YY HH24:MI:SS'
      )
   );
END;
/

Isn’t this fun?

19-14.

The four pieces of information displayed are:

30-JAN-99 -> 28-FEB-99
27-FEB-99 -> 27-MAR-99
31-JAN-99 -> 28-FEB-99
28-FEB-99 -> 31-MAR-99

19-15.

Statement (b) describes accurately the behavior of ADD_MONTHS when the date you pass to it is the last day of the month. This rule can result in less than desirable behavior, as shown in the previous problem. Specifically, if the number of days in the original date is less than the number of days in the resulting month, ADD_MONTHS treats “last day in month” as a logical condition and always returns the last day in the resulting month. So:

ADD_MONTHS ('28-FEB_99', 1)

results in a date value of March 31, 1999, rather than March 28, 1999. What if you want ADD_MONTHS to always return the “physically” matching date (i.e., the same day number in the resulting month)? See the “Expert” section for such a challenge.

19-16.

Here is the database trigger:

CREATE OR REPLACE TRIGGER for_each_new_employee
   BEFORE insert
   ON employee FOR EACH ROW
BEGIN
   :new.hiredate := ROUND (:new.hire_date, 'HH'),
END;
/

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

19-18.

You can take one of two approaches:

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;
BEGIN
   ln_diff := ABS (TO_NUMBER (ld_date2 - ld_date1)) + 1;
   IF ld_date1 < ld_date2
   THEN
      ld_date := ld_date1;
   ELSE
      ld_date := ld_date2;
   END IF;

   ln_bus_days := ln_diff;
   FOR i IN 1 .. ln_diff
   LOOP
      IF RTRIM (UPPER (TO_CHAR (ld_date + i - 1, 'DAY'))) IN
            ('SATURDAY', 'SUNDAY')
      THEN
         ln_bus_days := ln_bus_days - 1;
      END IF;
   END LOOP;
   RETURN ln_bus_days;
END;
/

The following function implements the smart method; it is the more concise and efficient of the two and was created by Solomon Yakobson:

/* Filename on web page: bizbetwn.sf */
/* Provided by Solomon Yakobson */
CREATE OR REPLACE FUNCTION bizdays_between (
   start_date IN DATE, end_date IN DATE)
   RETURN INTEGER
IS
   v_sundays INTEGER :=
     NEXT_DAY (end_date - 7, 'SUNDAY') -
     NEXT_DAY (start_date - 1, 'SUNDAY'),

   v_saturdays INTEGER :=
     NEXT_DAY (end_date - 7, 'SATURDAY') -
     NEXT_DAY (start_date - 1, 'SATURDAY'),
BEGIN
   RETURN (
      end_date -
      start_date -
      (v_sundays + v_saturdays)/7 - 1);
END;
/

19-19.

Here is one possible implementation, building upon the smart method (in this version, I have also isolated the common “count for day” logic into its own function):

/* Filename on web page: bizbetwn3.sf */
CREATE TABLE holiday (dt DATE);

CREATE OR REPLACE FUNCTION daycount (
   start_date IN DATE, end_date IN DATE, dayname IN VARCHAR2)
   RETURN INTEGER
IS
BEGIN
   RETURN (
     NEXT_DAY (end_date - 7, dayname) -
     NEXT_DAY (start_date - 1, dayname)
     );
END;
/
CREATE OR REPLACE FUNCTION bizdays_between (
   start_date IN DATE, end_date IN DATE)
   RETURN INTEGER
IS
   holiday_count INTEGER;
BEGIN
   SELECT COUNT(*) INTO holiday_count
     FROM holiday
    WHERE dt BETWEEN start_date AND end_date;

   RETURN (
      end_date -
      start_date -
      (daycount (start_date, end_date, 'SUNDAY') +
       daycount (start_date, end_date, 'SATURDAY'))/7 - 1 - holiday_count);
END;
/

19-20.

One possible implementation is shown next. Here’s one complication to keep in mind: if you start on a Monday and ask NEXT_DAY to find the next Monday, it finds it one week in the future:

/* Filename on web page: nthday.sf */
CREATE OR REPLACE FUNCTION nthday (
   yourdate IN DATE, dayname IN VARCHAR2, nthday IN INTEGER := 1)
   RETURN DATE
IS
   /* Start with last day of previous month. */
       retval DATE := TRUNC (yourdate, 'MONTH') - 1;
BEGIN
   /* Start search in "nth week". */
   RETURN NEXT_DAY (retval + (nthday - 1) * 7, dayname);
END;
/

19-21.

Here is a brute force implementation I threw together:

/* Filename on web page: maxdt1.sql */
DECLARE
    old_date DATE;
    curr_date DATE := SYSDATE;
BEGIN
    LOOP
old_date := curr_date;
      curr_date := curr_date + 1;
    END LOOP;
EXCEPTION
     WHEN OTHERS
     THEN
         DBMS_OUTPUT.PUT_LINE (SQLERRM);
         DBMS_OUTPUT.PUT_LINE ('Last date: ' ||
            TO_CHAR (old_Date, 'MM-DD-YYYY'));
END;
/

A number of my readers have pointed out that this is a rather slow implementation and offered dramatically more efficient versions. Here is one:

/* Filename on web page: maxdt2.sql */
DECLARE
   /* Initial implementation by Eva Blinder, modified by SF. */
   curr_date DATE := SYSDATE;
   last_date DATE;
   step PLS_INTEGER := 1000;
BEGIN
   LOOP
      BEGIN
         last_date := curr_date;
         curr_date := curr_date + step;
      EXCEPTION
         WHEN OTHERS
         THEN
            IF step = 1
            THEN
               DBMS_OUTPUT.put_line (
                  'Last date: ' ||
                  TO_CHAR (
                     last_date,
                     'Month DD, YYYY'
                  )
               );
               EXIT;
            ELSE
               step := step / 2;
               curr_date := last_date;
            END IF;
      END;
   END LOOP;
END;

/

In this algorithm, the potential last date is moved forward by 1,000 days at a time. When you hit an error, you go back to the last good date and step forward by half that number of days. Keep doing that (reducing your step forward by half) until the step is 1, and that means you’ve gone as far forward as possible.

19-22.

Here is one possible implementation:

/* Filename on web page: addmths.sf */
CREATE OR REPLACE FUNCTION new_add_months (
   date_in IN DATE, months_shift IN NUMBER)
   RETURN DATE
IS
   retval DATE;
BEGIN
   retval := ADD_MONTHS (date_in, months_shift);

   /* Is original date the last day of its month? */
   IF date_in = LAST_DAY (date_in)
   THEN
      retval :=
         LEAST (retval,
            TO_DATE (
               TO_CHAR (date_in, 'DD') ||
               TO_CHAR (retval, 'MMYYYY'),
               'DDMMYYYY'));
   END IF;

   /* Return the shifted date */
   RETURN retval;
EXCEPTION
   WHEN OTHERS THEN RETURN retval;
END new_add_months;
/

The core logic here says that if the date you supplied is the last day of its month, construct a “physical” last day in the resulting month. If that day number exceeds the last actual day in the month, let the exception section trap the error and return the ADD_MONTHS standard value. Otherwise, return the earliest of the ADD_MONTHS value (the last day in the month) and the date with the matching day number.

When you run the same lastday.sql script (saved to lastday2) using new_add_months instead of ADD_MONTHS, here are the results:

SQL> @lastday2
30-JAN-99 -> 28-FEB-99
27-FEB-99 -> 27-MAR-99
31-JAN-99 -> 28-FEB-99
28-FEB-99 -> 28-MAR-99

As you can see, 28-FEB-99 now shifts to 28-MAR-99, which is the desired behavior.

19-23.

One possible implementation, relying on an index-by table of formats, is in the datemgr.pkg file on the book’s web page.

You could also store the various formats in a database table and then use a query to loop through the valid format masks. This is more flexible (change the formats through SQL or a GUI interface: no need to change the code), but much slower. A middle-ground approach is to store the formats in a table, but read them into an index-by table in the package initialization section.