Date conversion problems.

Hi all, I am back to this list after a long long time. There seems to be some problems with the date increment functions in Informix Online 7.3 If i write a statement like this Let p_date = "29-02-2000" + 1 UNITS YEAR 29-02-2000 means 29th february 2000. It gives an error like ---------- FORMS statement error number -1267. The result of a datetime computation is out of range. ---------- If i write this in sql then i get an error like this ------- select date("29-02-2000") + 1 units year from dummy # ^ # 1267: The result of a datetime computation is out of range. # ------- Have anyone of u encountered the similar error. Also what is the reason and solution to this. Thanks in Advance With Regards Nayan Jain ! - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - "By working faithfully eight hours a day, you may eventually get to be a boss and work twelve hours a day."

In article <81h1at$grq$1@news.xmission.com>, nayan jain <nayan.jain@tatainfotech.com> wrote: > > > Hi all, > > I am back to this list after a long long time. > > There seems to be some problems with the date increment functions in > Informix Online 7.3 > > If i write a statement like this > > Let p_date = "29-02-2000" + 1 UNITS YEAR > > 29-02-2000 means 29th february 2000. > > It gives an error like > > ---------- > FORMS statement error number -1267. > The result of a datetime computation is out of range. > ---------- > > If i write this in sql then i get an error like this > > ------- > select date("29-02-2000") + 1 units year from dummy > # ^ > # 1267: The result of a datetime computation is out of range. > # > ------- > > Have anyone of u encountered the similar error. > > Also what is the reason and solution to this. This is expected behavior. Feb. 19, 2000 plus 1 year is Feb. 29, 2001 which is indeed an invalid date since 2001 is NOT a leap year and there IS NO Feb. 29, 2001! What would you like Informix to return? For some the correct answer is Feb. 28, 2001 since that is the last day in Feb. in that year. For others the correct value would be Mar. 1, 2001 as the day after Feb. 29, 2001 literally 1 year and a day after Feb. 28, 2000. One solution is to do this as a datetime and add INTERVAL (365) DAYS(3) TO DAYS that will always work reasonably. Another method that will always return a reasonable date is to adjust to the beginning of either the current or next month, add 1 year, then adjust forward or back the desired number of days. So: adate = date( "2/29/2000" ) mdays = monthdays( adate ) mnth = month( adate ) yr = year( adate ) yr = yr + 1 nextdt = mdy( mnth, 1, yr ) -- Create a date 1 year after month begin nextdt = nextdt + days( mdays ) -- Add days back, if leap year 29th -- if not leap year, 1st of next month This is pseudocode and does not take the various datatypes or syntax into consideration but you get the idea. Art S. Kagel Sent via Deja.com http://www.deja.com/ Before you buy.