Issue calculating month end date in 4GL

Question

This routine is returning 12/31/2016 instead of 12/31/2015 and messing up a report. Any idea where it is going wrong?

    LET date_month = MONTH(p_selection.date_from)
IF  date_month = 12 THEN 
    LET date_month = 1
    LET p_selection.date_from = p_selection.date_from + 1 UNITS YEAR
    LET date_thru = date_month,"/01/",YEAR(p_selection.date_from)
    LET p_selection.date_from = p_selection.date_from - 1 UNITS YEAR
ELSE 
    LET date_month = date_month + 1
    LET date_thru = date_month,"/01/",YEAR(p_selection.date_from)
END IF

LET p_selection.date_thru = date_thru CLIPPED

IF YEAR(p_selection.date_thru) <> YEAR(p_selection.date_from) THEN 
    LET p_selection.date_thru = p_selection.date_thru + 1 UNITS YEAR
END IF 
    LET p_selection.date_thru = p_selection.date_thru - 1

Answer 1

I had

FUNCTION last_of_month(m,y)
DEFINE m,y SMALLINT
    RETURN MDY(m,days_in_month(m,y),y)
END FUNCTION

with an appropriate function days_in_month() to return 28,29,30,31 as appropriate based on month and year

For the OP you should be creating a function and re-using it, rather than potentially repeating logic like it looks like you are doing.

What I really wanted to comment on, was that at Genero we were discussing wether to add built-in functions to do these types of calculations. If everyone has their own first_of_month, last_of_month, add_months then the thought was we should include them in the language rather than having everyone reinvent the wheel. As part of the discussions, what I learned was that the + 1 UNITS MONTH, as used by RET in his answer is as he says leap-year safe. For some reason I thought it wasn't and had tended to avoid it, and write in the style of JLefflers answer. Looking at other 4GL/Genero customers library code I have seen, I suspect other developers thought the same thing.

Answer 2

Here's some old code that's not seen the light of day in a while, but is still valid I4GL.

ltmonth.4gl

{
    @(#)$Id: ltmonth.4gl,v 1.4 1990/04/05 11:02:05 john Exp $
    @(#)Sphinx Informix Tools: General Library
    @(#)Find last day of this month
    @(#)Author: JL
}

FUNCTION last_of_this_month(edate)

    DEFINE
        edate   DATE        { Effective date (frequently TODAY) }

    IF edate IS NULL THEN
        RETURN edate
    END IF

    RETURN first_of_next_month(edate) - 1

END FUNCTION {last_of_this_month}

fnmonth.4gl

{
    @(#)$Id: fnmonth.4gl,v 1.4 1990/04/05 11:02:03 john Exp $
    @(#)Sphinx Informix Tools: General Library
    @(#)Find 1st of next month
    @(#)Author: JL
}

FUNCTION first_of_next_month(edate)

    DEFINE
        edate   DATE,       { Effective date (frequently TODAY) }
        mm      INTEGER,    { Month number }
        yy      INTEGER     { Year }

    IF edate IS NULL THEN
        RETURN edate
    END IF

    LET mm = MONTH(edate) + 1
    LET yy = YEAR(edate)
    IF mm > 12 THEN
        LET mm = 1
        LET yy = yy + 1
    END IF

    RETURN MDY(mm, 1, yy)

END FUNCTION {first_of_next_month}

lastthismonth.spl

And here's some SPL based on the I4GL above:

-- @(#)$Id: lastthismonth.spl,v 1.2 2008/07/20 02:54:37 jleffler Exp $
--
-- @(#)Create last_of_this_month Stored Procedure
--
-- @(#)Version: ltmonth.4gl,v 1.4 1990/04/05 11:02:05 john
-- @(#)Sphinx Informix Tools: General Library
-- @(#)Find last day of this month
-- @(#)Author: JL
--
-- Alternative expression: 
-- (MDY(MONTH(dateval), 1, YEAR(dateval)) + 1 UNITS MONTH) - 1 UNITS DAY

CREATE PROCEDURE last_of_this_month(edate DATE DEFAULT TODAY)
    RETURNING DATE AS last_of_this_month;

    IF edate IS NULL THEN
        RETURN edate;
    END IF

    RETURN first_of_next_month(edate) - 1;

END PROCEDURE {last_of_this_month};

firstnextmonth.spl

-- @(#)$Id: firstnextmonth.spl,v 1.1 2008/07/20 02:21:13 jleffler Exp $
--
-- @(#)Create first_of_next_month Stored Procedure
--
-- @(#)Version: fnmonth.4gl,v 1.4 1990/04/05 11:02:03 john
-- @(#)Sphinx Informix Tools: General Library
-- @(#)Find 1st of next month
-- @(#)Author: JL

CREATE PROCEDURE first_of_next_month(edate DATE DEFAULT TODAY)
    RETURNING INTEGER AS first_of_next_month;

    DEFINE mm   INTEGER;    { Month number }
    DEFINE yy   INTEGER;    { Year }

    IF edate IS NULL THEN
        RETURN edate;
    END IF

    LET mm = MONTH(edate) + 1;
    LET yy = YEAR(edate);
    IF mm > 12 THEN
        LET mm = 1;
        LET yy = yy + 1;
    END IF

    RETURN MDY(mm, 1, yy);

END PROCEDURE {first_of_next_month};

Note the alternative expression. It works, but it is an incredible mixture of DATE and DATETIME calculations.

Answer 3

Assuming the input p_selection.date_from is 12/01/2015 ...

IF date_month = 12 returns TRUE, so date_thru gets calculated as 01/01/2016

But then the second IF statement also returns TRUE, adding another year to p_selection.date_thru (01/01/2017), before decrementing it one day to 12/31/2016.

Seems to me that someone has had several goes at trying to calculate the date of the last day of the month, and one or the other method is required, not both. The one that appears first in your code is particularly dubious - there's an assumption that DBDATE is US format when it casts between DATE and CHAR, which is entirely unnecessary.

A far simpler solution would be to simply calculate:

LET p_selection.date_thru = 
    MDY(MONTH(p_selection.date_from), 1, YEAR(p_selection.date_from))
    + 1 UNITS MONTH - 1 UNITS DAY

In other words find the first day of the selected month, add a month and subtract a day. Simple and robust, it works at the year boundary and when a leap day occurs.