From: dberg@informix.com (David Berg)
Newsgroups: comp.databases.informix
Subject: Date calculation functions
Date: 30 Oct 1996 21:49:52 GMT

Following is a stored procedure that increments/decrements a date by a
specified number of months, returning the last day of the computed month
if the [in|de]cremented date is invalid. This particular procedure deals
with a DATE variable; you can tune it to deal with a DATETIME variable
and do other magical things if you wish.

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

CREATE PROCEDURE incr_date(pBeg_date DATE, pIncr_mos INTEGER)
       RETURNING DATE;

-- Increment or decrement a date by a specified number of months. If the
-- computed date is beyond the last day of the month, return the last day
-- of the month.

DEFINE pComp_date  DATE;
DEFINE pAdj_days   SMALLINT;

BEGIN
      LET pAdj_days = 0;
      WHILE 1 = 1

          -- If the computed day is beyond the last day of the month,
          -- then subtract one day at a time until we find the last day
          -- of that month.

          ON EXCEPTION IN (-1267)
              LET pAdj_days = pAdj_days + 1;
          END EXCEPTION

          LET pComp_date =
              DATE (EXTEND (pBeg_date, YEAR TO DAY)
                    - pAdj_days UNITS DAY
                    + pIncr_mos UNITS MONTH);
          EXIT WHILE;
      END WHILE

      RETURN pComp_date;

END

END PROCEDURE;

--
  ___                   ___              Principal Consultant
  /  ) __      . __/    /_ ) _    _  __  Informix Software Inc. (303) 850-0210
_/__/ (_(_ (/ / (_(_  _/__) (-' ~/ '(_-  5299 DTC Blvd #740 Englewood CO 80111
dberg@informix.com                       Opinions expressed herein are my own.

Eventually all things merge into one...and a river runs through it. -N.Maclean