Adding months to a date 1

[S1P]

Hi

I am new to 4gl. So this maybe a really silly question. But I am trying to add months to a date and it is not working. Is there a function out there or do I need to write my own, to take in to account days in months etc.... any help would be much appreciated.

S1P

 

[edmorris]

this is informix - of course you have to write your own!

so the date 10/31/2003 plus 4 months should equal what? 2/29/2004?

you'd think doing this would be ok:

display MDY ( month(in_date)+n_months, day(in_date), year(in_date) )

...but it's NOT ok for days that don't exist in short months. Informix doesn't handle this because it doesn't know how you are defining a month. Is a month 30 days, 28+x days, 4 weeks or 365/12? should I return the last day of the month if the day is invalid? etc.
The duration of a month is open to interpretation and therefore up to you to implement the appropriate rule.

you'd also have to account for year-wrapping and leap-years (e.g. date in the above example).

p.s. how about showing it without the day, like this:
(10/2003 + 4 months = 2/2004)

good luck.
-Ed

 

[S1P]

Thanks ed for you help. The function I have wrote, does anyone out there know of a quicker/better way to perform this all advice is more than welcome.

FUNCTION add_month_to_date( ps_no_of_months=4 )

DEFINE
ps_no_of_months SMALLINT

DEFINE
ls_day SMALLINT,
ls_month SMALLINT,
ls_year SMALLINT,
ls_loopday SMALLINT,
ld_today DATE,
ld_tdate DATE,
lc_date CHAR(10)

INITIALIZE ls_day, ls_month, ls_year, ld_date TO NULL
LET lc_date = ""
LET ld_today = "31/10/2003"

LET l_tdate = ld_today + ps_no_of_months UNITS MONTH

IF ld_tdate IS NULL THEN
LET ls_day = DAY(ld_today)
LET ls_month = MONTH(ld_today)
LET ls_year = YEAR(ld_today)
LET ls_month = ls_month + ps_no_of_months
IF ls_month > 12 THEN
LET ls_year = ls_year + 1
LET ls_month = ls_month - 12
END IF
LET lc_date = ls_day USING "&&", "/",
ls_month USING "&&", "/",
ls_year USING "&&&&"
LET ld_tdate = lc_date
IF ld_tdate IS NULL THEN
FOR ls_loopday = 31 TO 28 STEP - 1
LET lc_date = ls_loopday USING "&&", "/",
ls_month USING "&&", "/",
ls_year USING "&&&&"
LET ld_tdate = lc_date
IF ld_tdate IS NOT NULL THEN
EXIT FOR
END IF
LET lc_date = ""
END FOR
END IF
END IF
RETURN ld_tdate
END FUNCTION

 

[olded]

Hi:

First, native Informix 4GL is too brain dead to support this syntax:

FUNCTION add_month_to_date( ps_no_of_months=4 )

It must be:

FUNCTION add_month_to_date(ps_no_of_months)

I'm not certain what you're doing, but it looks like you are adding ps_no_of_months to the current date, and you're generating a lot of error checking if the date is null after the addition. You don't need all this error checking because the informix date arithmetic is solid if the integer is defined.

Why don't use the today function to get the current date:

let ld_today = today # this is guaranteed to be defined.

Now, all you have to worry about is whether ps_no_of_months is defined - non null.

IF ps_no_of_months IS NOT NULL
THEN
LET ld_today = ld_today + ps_no_of_months
END IF

Then, just return ld_today.

Regards,


Ed