Re: How to select date + 1 month ?

In article <CBuxA6.25o@ibg1.gtn.com> ado@ibg1.gtn.com (Christoph Adomeit) writes: >I wonder if there is any way to directly select a date + xx month from an >informix sql-table. Sure: select <datetimecolumn> + XX UNITS MONTH FROM <tablename> >I could select a "16.8.1993" + 30 from anytable which adds 30 days to the date, >but is there also a way to do this with month's or years ? Sure, just use UNITS. >This would be much easier than programming lots of date routines. > >Thanks > Christoph The only thing you need to watch for is that you don't compute your way into an impossible date, e.g. you can't directly add one month to January 31. Here's an example of an algorithm for adding a month such that if directly adding a month puts you beyond the end of the following month, it yields the last day of the following month instead (e.g. 1/30/93 + 1 month -> 2/28/93). ------------------------------------------------------------------------ main define dv,result datetime year to second define dd, firstofmo, lastofprior date whenever any error continue while (true) prompt "Enter date: " for dd let dv = dd -- convert to datetime let result = dv + 1 units month -- try adding a month. This will work -- all but 6 or 7 days of the year! if status != 0 -- if out of range, do the hard way then -- get the last day of next month let result = dv + 2 units month -- add 2 months (always works) let firstofmo = mdy(month(result), 1, year(result)) -- DATE of 1st day of second month from now let lastofprior = firstofmo - 1 let result = lastofprior -- convert to datetime end if display "datetime + 1 Popiel-month = ", result display " " end while end main ------------------------------------------------------------------------ -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland Disclaimer: Sender under influence of 1,3,7-trimethylxanthine "Those who say that the 21st century begins on January 1, 2001 are technically correct, and they will be a year late for one hell of a big party." -- Seth Breidbart