sql - Function Add Month in informix -

i need function adds current date 1 month. so:

let _date =     mdy(month(current_date)+1, day(current_date), year(current_date)); 

but there problem months in days <31

you'll need check whether works in version, should result want if have sufficiently modern version of informix (11.70.fc5 or later, or version 12.10.fc1 or later) , use datetime arithmetic.

let next_month = extend(current_date, year day) + 1 units month 

older versions of informix baulk if day out of range target month.

---

testing on 12.10 server, used sql:

create table dl (dv date not null primary key); insert dl values('2012-01-28'); insert dl values('2012-01-29'); insert dl values('2012-01-30'); insert dl values('2012-01-31'); insert dl values('2012-02-01');  select dv, extend(dv, year day) + 1 units month dl; 

given run dbdate=y4md- set in environment, output was:

2012-01-28    2012-02-28 2012-01-29    2012-02-29 2012-01-30    2012-02-29 2012-01-31    2012-02-29 2012-02-01    2012-03-01 

this testing 12.10.fc5 version. can confirm 11.70.fc4 error on addition of 1 month 2012-01-30. reviewed code while writing addendum answer; fixed in march 2012 11.70 fixpack. afaict, first fix pack after check in 11.70.fc5. since have 11.70.fc3, old behaviour — deliberate design decision circa 1990, not bug per se — still in product.