12.2.1 Customizing the Behavior of ADD_MONTHS

As noted earlier, if you pass a day to ADD_MONTHS which is the last day in the month, PL/SQL always returns the last day in the resulting month, regardless of the number of actual days in each of the months. While this may work perfectly well for many, if not most, Oracle installations, I have encountered at least one company in the insurance industry that definitely cannot use ADD_MONTHS the way it works by default. At this site, if I am on the 28th day of February and shift forward a month, I need to land on the 28th of March -- not the 31st of March. What's a programmer to do?

The best solution is to write your own version of ADD_MONTHS that performs the way you want it to, and then use it in place of ADD_MONTHS. The following example shows a new_add_months function. It always lands you on the same day in the month, unless the original day does not exist in the new month, in which case the day is set to the last day in the new month.

This code uses the LAST_DAY function to see if the original date falls on the last day of that month:

/* Filename on companion disk: addmths.sf */
CREATE OR REPLACE FUNCTION new_add_months (date_in IN DATE, months_shift IN
NUMBER)
   RETURN DATE
IS
   /* Return value of function */
   return_value DATE;

   /* The day in the month */
   day_of_month VARCHAR2(2);

   /* The month and year for the return value */
   month_year VARCHAR2(6);

   /* The calculated end of month date */
   end_of_month DATE;
BEGIN
   return_value := ADD_MONTHS (date_in, months_shift);

   /* Is original date the last day of its month? */
   IF date_in = LAST_DAY (date_in)
   THEN
      /* Pull out the day number of the original date */
      day_of_month := TO_CHAR (date_in, 'DD');

      /* Grab the month and year of the new date */
      month_year := TO_CHAR (return_value, 'MMYYYY');

      /* Combine these components into an actual date */
      BEGIN
         end_of_month := TO_DATE (month_year || day_of_month, 'MMYYYYDD');
         /*
         || Return the earliest of (a) the normal result of ADD_MONTHS
         || and (b) the same day in the new month as in the original month.
         */
         return_value := LEAST (return_value, end_of_month);
      EXCEPTION
         WHEN OTHERS THEN NULL;
      END;
   END IF;

   /* Return the shifted date */
   RETURN return_value;
END new_add_months;

Take a look at the difference between ADD_MONTHS and new_add_months:

ADD_MONTHS ('31-JAN-1995', 1) ==> 28-FEB-1995
new_add_months ('31-JAN-1995', 1) ==> 28-FEB-1995

ADD_MONTHS ('28-FEB-1994', 2) ==> 30-APR-1994
new_add_months ('28-FEB-1994', 2) ==> 28-APR-1995

The above function can be used in a PL/SQL program like the following:

IF new_add_months (order_date, 3) > SYSDATE
THEN
   ship_order;
END IF;

If you want new_add_months to also accept the two arguments in either date-number or number-date order, you need to place the function inside a package and then overload the function definition, as shown below; see Chapter 16, Packages , for more information on constructing packages and overloading module definitions.

PACKAGE date_pkg
IS
   FUNCTION new_add_months (date_in IN DATE, months_shift IN NUMBER)
      RETURN DATE;

   FUNCTION new_add_months (months_shift IN NUMBER, date_in IN DATE)
      RETURN DATE;
END;

If you are using PL/SQL Release 2.1 or beyond, you can use this substitute for ADD_MONTHS in your SQL DML statements, as well as your PL/SQL programs:

SELECT new_add_months (SYSDATE, 3) FROM dual;

A final observation: the unexpected behavior of ADD_MONTHS for the last day in a month demonstrates once again that it is always a good idea to test both your programs and the programs of others at their limits. Don't assume that a program will work in any particular fashion until you test it. In this case, if the program shifts dates by months, then be sure to test for the end and beginning of months.

12.2.2 Using NEW_TIME in Client-Server Environments

One issue to keep in mind with SYSDATE is that it will always reflect the date and time on the server and not on the individual client workstation or computer (unless, of course, your workstation is also the server). This may not be an issue when all machines are located in the same general vicinity, but when you have a server in New York and a client in Iowa, the times will definitely not match up. This is a more difficult problem to resolve.

You can use the NEW_TIME date function to convert the date and time returned by SYSDATE to the actual date and time in the client's location. To do this you need to know the time zones in each of these locations. The best way to be sure the time zones are available is to store them in a configuration table; the zones may then be read into some kind of global variables when an application is initiated. In PL/SQL, you would do this with package variables.

Note that this example relies heavily on the package structure, which is explained in Chapter 16 .

In the following examples, I will store the client and server time zone values directly in PL/SQL variables and provide a way to change them if necessary. I will then build a function called system_date, which replaces the SYSDATE function. The objectives of this function are twofold:

1. Keep to a minimum the number of times that SYSDATE is called.

2. Adjust the time automatically to account for time zone changes.

The tz package shown below provides a set of procedures and functions to manage both the system date and the client and server time zones. Users of the package can access the package data only through the functions (retrieval) and the procedures (change values). The main module in the package is system_date; its specification follows:

FUNCTION system_date
   (refresh_in IN VARCHAR2 := 'NOREFRESH',
    server_time_zone IN VARCHAR2 := server,
    client_time_zone IN VARCHAR2 := client)
RETURN DATE;

This package-based version of SYSDATE takes up to three parameters:

refresh_in

If you need the current time or want to update the global current date value, then you really do want SYSDATE to be called again. If you refresh, then SYSDATE is used to update the package globals.

server_time_zone

The time zone of the server; the default is the packaged value.

client_time_zone

The time zone of the client; the default is the packaged value.

The tz package relies on the following global variables inside the package to keep track of the current date/time and the default client and server time zones:

system_date_global DATE := SYSDATE;
client_tz   VARCHAR2(3) := 'AST';
server_tz   VARCHAR2(3) := 'PST';

The very first time the system_date function is called, the package will be loaded into memory and these variables assigned their default values. I can now call system_date using both of the default configuration time zones -- and I will not get a new time computed each time I do so:

IF tz.system_date
   BETWEEN '15-JAN-1994' AND '22-JAN-1994'
THEN
   ...
END IF;

Or I can override the default time zones with Greenwich Mean and Newfoundland Standard times, also requesting a refresh of the time:

current_date :=
   tz.system_date ('REFRESH', 'GMT', 'NST');

If you do not want to have to specify the package name, tz, in front of the system_date function name, you can create a standalone stored procedure of the same name which, in effect, hides the package ownership:

FUNCTION system_date
   (refresh_in IN VARCHAR2 := 'NOREFRESH',
    server_time_zone VARCHAR2 := tz.server,
    client_time_zone VARCHAR2 := tz.client)
   RETURN DATE
IS
BEGIN
   RETURN tz.system_date
      (refresh_in, server_time_zone, client_time_zone);
END;

The following sections contain the code for the specification and body of the tz package.