CODEWITHSUNDEEP
sqlinformix
Dmitriy
Dmitriy

Reputation: 133

Function Add Month in informix

I need a function that adds the current date to 1 month. I do so:

let _date =     MDY(MONTH(current_date)+1, DAY(current_date), YEAR(current_date));

But there is a problem with the months in which the days <31

Upvotes: 2

Views: 4090

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753655

You'll need to check whether it works in your version, but you should get the result you want if you have a sufficiently modern version of Informix (11.70.FC5 or later, or version 12.10.FC1 or later) and you use DATETIME arithmetic.

LET next_month = EXTEND(current_date, YEAR TO DAY) + 1 UNITS MONTH

Older versions of Informix will baulk if the day is out of range for the target month.

Testing on a 12.10 server, I used this SQL:

create table dl (dv date not null primary key);
insert into dl values('2012-01-28');
insert into dl values('2012-01-29');
insert into dl values('2012-01-30');
insert into dl values('2012-01-31');
insert into dl values('2012-02-01');

SELECT dv, EXTEND(dv, YEAR TO DAY) + 1 UNITS MONTH FROM dl;

Given that I run with DBDATE=Y4MD- set in my environment, the 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 was testing with a 12.10.FC5 version. I can confirm that with 11.70.FC4 I get an error on the addition of 1 month to 2012-01-30. I reviewed the code while writing this addendum to my answer; it was fixed in March 2012 for an 11.70 fixpack. AFAICT, the first fix pack after the check in was 11.70.FC5. Since you have 11.70.FC3, the old behaviour — a deliberate design decision from circa 1990, not a bug per se — was still in the product.

Upvotes: 3

Related Questions