This section contains more detailed examples of some of the functions summarized in this chapter.
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;
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.
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:
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:
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.
/* Filename on companion disk:tz.spp */ PACKAGE tz IS /* Return the client timezone */ FUNCTION client RETURN VARCHAR2; /* Return the server timezone */ FUNCTION server RETURN VARCHAR2; /* || Retrieve system date. Can ask to refresh the value || from the database and also over-ride the default || time zones, just as you would with NEW_TIME. */ FUNCTION system_date (refresh_in IN VARCHAR2 := 'NOREFRESH', server_time_zone IN VARCHAR2 := server, client_time_zone IN VARCHAR2 := client) RETURN DATE; /* Change the client timezone */ PROCEDURE set_client (tz_in IN VARCHAR2); /* Change the server timezone */ PROCEDURE set_server (tz_in IN VARCHAR2); END tz;
/* Filename on companion disk: tz.spp */ PACKAGE BODY tz IS /* The actual "global" variables stored in the package */ system_date_global DATE := SYSDATE; client_tz VARCHAR2(3) := 'AST'; server_tz VARCHAR2(3) := 'PST'; FUNCTION client RETURN VARCHAR2 IS BEGIN RETURN client_tz; END; FUNCTION server RETURN VARCHAR2 IS BEGIN RETURN server_tz; END; FUNCTION system_date (refresh_in IN VARCHAR2 := 'NOREFRESH', server_time_zone IN VARCHAR2 := tz.server, client_time_zone IN VARCHAR2 := tz.client) RETURN DATE IS BEGIN /* Update the system date global if requested */ IF UPPER (refresh_in) = 'REFRESH' THEN tz.system_date_global := SYSDATE; END IF; /* Use NEW_TIME to shift the date/time */ RETURN NEW_TIME (system_date_global, server_time_zone, client_time_zone); END; PROCEDURE set_client (tz_in IN VARCHAR2) IS BEGIN assert (valid_time_zone (tz_in )); client_tz := tz_in; END; PROCEDURE set_server (tz_in IN VARCHAR2) IS BEGIN assert (valid_time_zone (tz_in )); server_tz := tz_in; END; PROCEDURE assert( condition_in IN BOOLEAN ) IS BEGIN IF NOT condition_in THEN RAISE VALUE_ERROR; END IF; END; FUNCTION valid_time_zone( time_zone_in IN VARCHAR2 ) RETURN BOOLEAN IS validation_tz VARCHAR2(3) := 'EST'; -- a valid time zone validation_date DATE; invalid_time_zone EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_time_zone, -1857); BEGIN validation_date := NEW_TIME( SYSDATE, time_zone_in, validation_tz ); RETURN TRUE; EXCEPTION WHEN invalid_time_zone THEN RETURN FALSE; END; END tz;
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.