Re: Why the following.......?

This is the classic problem with month math. Your problem of May 31, 1996 - 13 months - you might expect it to return April 30, 1995. On the other hand, perhaps you were looking for May 1, 1995. The problem is that whenever you do month math, and both months do not contain the same days (happens with 29th, 30th, and 31st of months) the answer is undefined so you get an error. The way around it in 4gl is to write a function which does date math. In sql you could do it with a stored procedure. The algorithm I've seen and liked the most is to convert a date to the first day of the month and then subtract a day to get the last day of the previous month. Good luck! Regards, Cathy -------------------------------------------------------------------------------- Cathy Kipp E-mail: ckipp@verinet.com Fort Collins, Colorado, USA -------------------------------------------------------------------------------- In article <4ps92t$fl0@hal.cs.depaul.edu>, Brian Li <cphdcbl@ted.cs.depaul.edu> wrote: >Dear netters, I am trying to calculate the date(13 months back) by >do the following sql and informix is returning the following error. > >Anyone has idea why it is so? > >Thanks in advance. > >Brian Li >------------------------------------------------------------ >select >datetime(1996-05-31) year to day - interval(1-1) year to month >from tbsctupd >where >subsystem_id_ind = 'j' and >subfunction_code = 'date'; ># ^ ># 1267: The result of a datetime computation is out of range. ># >