Re: Date calculation functions : Informix bug?

> > In article <5408pv$i9u@cssun.mathcs.emory.edu>, > jparker@boi.hp.com (Jack Parker) wrote: > > ##################################################################### > > FUNCTION Add_Month (bdate, num_months) > > > > DEFINE bdate, edate DATE, num_months SMALLINT > > > > LET edate = bdate + num_months UNITS MONTH > > RETURN edate > > > > END FUNCTION > I am running Informix 4.1 tools at our site and we, some six months back, > joyfully changed our date computation code to use syntax similar to the > above. > > Unfortunately it does not work. > e.g '31/10/96 + 1 UNITS MONTH' gives an error > (Form 1267 : The result of a datetime computation is out of range) > Informix seems to be trying to return 31/11/96 > > The same problem occurs with UNITS YEAR if a leap year is involved. > > I'm curious to know if this is a version 4.1 problem alone. > > Rudy > Rudy worked out a solution to this problem which I have since included in the datelib.4gl file. I am awiating other problems before forwarding that file to the archives. His solution: ##################################################################### FUNCTION Add_Month (l_fromdt, l_months) ##################################################################### # Rudy Fernandes 10/96 # Get the number of days that l_fromdt is offset from the first of its month # Use the UNITS MONTH as follows, without worry of an error, to get # the first day of the month of the result date # Compare the MONTH of l_todt with that of (l_todt + offset) days # If in the same month, no sweat, RETURN (l_todt + l_offset) # If not, RETURN the last day of the month which ends before (l_todt + l_offset) DEFINE l_fromdt DATE, l_months SMALLINT, l_todt DATE, l_offset SMALLINT LET l_offset = l_fromdt - MDY(MONTH(l_fromdt),1,YEAR(l_fromdt)) LET l_todt = MDY(MONTH(l_fromdt), 1, YEAR(l_fromdt)) + l_months UNITS MONTH IF MONTH(l_todt) = MONTH (l_todt + l_offset) THEN LET l_todt = l_todt + l_offset ELSE LET l_todt = MDY (MONTH(l_todt+l_offset), 1, YEAR(l_todt+l_offset)) - 1 END IF RETURN l_todt END FUNCTION ###################################################################### cheers j. ________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com Currently on loan to PLD/PE ________________________________________________________________________ Outside of a dog a book is a man's best friend. Inside of a dog it's too dark to read. (Groucho Marx) ________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. ________________________________________________________________________