Date calculations

[mmarkows]

Hi,

I have the following problem: I have to calculate the first and the last day of the month, which is X months before given date.

For example: today is the 6th of February, 2007, and I have to find the first and the last day of December 2006 (given date - 2 mths) to extract payment components.

Can I do that without building dictionary table with calendar?

Thanks in advance. Regards,
Maciej.

 

[TomBascom]

Without testing and making no attempt to be efficient:

define variable d as date no-undo.
define variable x as date no-undo.
define variable m as integer no-undo.
define variable y as integer no-undo.

d = 2/6/2007.

y = year( d ).
m = month( d ) - 2.

if m < 1 then
  do:
    m = m + 12.
    y = y - 1.
  end.

x = date( m, 1, y ).  /* the first of the month in question */

y = year( d ).
m = month( d ) - 1.

if m < 1 then
  do:
    m = m + 12.
    y = y - 1.
  end.

x = date( m, 1, y ).  /* the first of the month after the month in question */
x = x - 1.  /* the last day of the month before the month after the month in question */

If this doesn't actually work (after all, I'm just typing it ad-hoc, you should be able to fix it without too much trouble The key is to know about and use the year(), month() and date() functions.

 

[tsspdx]

In OE, how about ADD-INTERVAL(today, -2, 'MONTH')?

Don't have an OE system in front of me so can't vouch for the syntax.

--Ray Robert

 

[mmarkows]

Tom, thanks a lot for help. I've made it this way for the first day:
/************ FUNCTION GetFirstDay ********************/
FUNCTION GetFirstDay RETURNS DATE (INPUT i_date AS DATE, i_months AS INT).
DEFINE VARIABLE c_month AS INT.
DEFINE VARIABLE c_year AS INT.
DEFINE VARIABLE c_date AS DATE.
IF i_date = ? THEN DO:
c_month = 1.
c_year = 1970.
END.
ELSE IF i_months > 0 AND i_months < (12 - MONTH(i_date)) THEN DO:
c_month = MONTH(i_date) + i_months.
c_year = YEAR(i_date).
END.
ELSE IF i_months > 0 AND i_months > (12 - MONTH(i_date)) THEN DO:
c_month = MONTH(i_date) + i_months - 12.
c_year = YEAR(i_date) + 1.
END.
IF i_months < 0 AND ABSOLUTE(i_months) < MONTH(i_date) THEN DO:
c_month = MONTH(i_date) + i_months.
c_year = YEAR(i_date).
END.
ELSE IF i_months < 0 AND ABSOLUTE(i_months) > MONTH(i_date) THEN DO:
c_month = MONTH(i_date) + 12 + i_months.
c_year = YEAR(i_date) - 1.
END.
ELSE IF i_months < 0 AND ABSOLUTE(i_months) = MONTH(i_date) THEN DO:
c_month = 12.
c_year = YEAR(i_date) - 1.
END.
c_date = DATE(c_month,1,c_year).
RETURN(c_date).
END. /* Function GetFirstDay */

And for the last day:

/**************** FUNCTION GetLastDay ********************/
FUNCTION GetLastDay RETURNS DATE (INPUT i_date AS DATE, i_months AS INT).
DEFINE VARIABLE c_month AS INT.
DEFINE VARIABLE c_year AS INT.
DEFINE VARIABLE c_date AS DATE.
DEFINE VARIABLE temp_date AS DATE.
IF i_date = ? THEN DO:
c_month = 1.
c_year = 2040.
END.
ELSE IF i_months > 0 AND i_months < (12 - MONTH(i_date)) THEN DO: /* the same year, + mths */
c_month = MONTH(i_date) + i_months.
c_year = YEAR(i_date).
END.
ELSE IF i_months > 0 AND i_months > (12 - MONTH(i_date)) THEN DO: /* year + 1, + mths */
c_month = MONTH(i_date) + i_months - 12.
c_year = YEAR(i_date) + 1.
END.
IF i_months < 0 AND ABSOLUTE(i_months) < MONTH(i_date) THEN DO: /* the same year, - mths */
c_month = MONTH(i_date) + i_months.
c_year = YEAR(i_date).
END.
ELSE IF i_months < 0 AND ABSOLUTE(i_months) > MONTH(i_date) THEN DO: /* year - 1, - mths */
c_month = MONTH(i_date) + 12 + i_months.
c_year = YEAR(i_date) - 1.
END.
ELSE IF i_months < 0 AND ABSOLUTE(i_months) = MONTH(i_date) THEN DO:
c_month = 12.
c_year = YEAR(i_date) - 1.
END.

IF c_month = 12 THEN DO: /* we have to change year if it's December*/
temp_date = DATE(1, 1,c_year + 1).
END.
ELSE DO:
temp_date = DATE(c_month + 1, 1, c_year).
END.

c_date = temp_date - 1.
RETURN(c_date).
END. /* Function GetLastDay */

 

[KnutHandsome]

Hey, look what I did for an hour on Saturday night, when everyone cool was out partying...

/* A couple of reusable calendar functions */

FUNCTION LastDayOfMonth RETURNS DATE
    (idtDate AS DATE):

    DEF VAR iDaysInMonth AS INTEGER NO-UNDO EXTENT 12 INITIAL
        [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31].

    DEF VAR dtLastDayOfMonth AS DATE NO-UNDO. /* returned */

    /* Mostly, this will do it */
    ASSIGN 
        dtLastDayofMonth = DATE (MONTH(idtDate),
                                 iDaysInMonth[MONTH(idtDate)],
                                 YEAR(idtDate)
                                 ) NO-ERROR.

    IF ERROR-STATUS:NUM-MESSAGES > 0 THEN /* Tried to assign Feb 29 in non leap year */
        dtLastDayofMonth = DATE (2, 28, YEAR(idtDate)).

    RETURN dtLastDayOfMonth.


END FUNCTION.


FUNCTION AddMonths RETURNS DATE
    (idtDate AS DATE, iMonths AS INTEGER):

    DEF VAR dtNewDate AS DATE NO-UNDO. /* Returned */

    DEF VAR iTotalMonths AS INTEGER NO-UNDO.
    DEF VAR iYY AS INTEGER NO-UNDO.
    DEF VAR iMM AS INTEGER NO-UNDO.
    DEF VAR iDD AS INTEGER NO-UNDO.

    /* All we are doing is adding in base 12 */
    iTotalMonths = YEAR(idtDate) * 12 + MONTH(idtDate) + iMonths
        .

    /* Work out the new Year, Month, Day */
    ASSIGN
        iYY = TRUNCATE (iTotalMonths / 12, 0) /* Year = TotalMonths DIV 12 */
        iMM = iTotalMonths MOD 12             /* Months = remainder */
        iDD = DAY(idtDate)
        .

    /* Modulus gives 0 ... 11, we want 1 ... 12 */
    IF iMM = 0 THEN 
        ASSIGN
            iYY = iYY - 1
            iMM = 12
        .

    /* Try the date - if it doesn't work, day in old month > # of days in new month */
    ASSIGN dtNewDate = DATE(iMM, iDD, iYY) NO-ERROR.

    IF ERROR-STATUS:NUM-MESSAGES > 0 THEN /* round to last day of month */
        dtNewDate = LastDayOfMonth(DATE(iMM, 1, iYY)).

    RETURN dtNewDate.

END FUNCTION.



/* Now your particular requirements */

DEF VAR dtTest AS DATE NO-UNDO INITIAL 2/6/07 .
DEF VAR dtFirstOfNewMonth AS DATE NO-UNDO.



dtFirstOfNewMonth = addMonths(DATE(MONTH(dtTest), 1, YEAR(dtTest)),
                              -2).


MESSAGE dtFirstOfNewMonth SKIP LastDayOfMonth(dtFirstOfNewMonth)
    VIEW-AS ALERT-BOX INFO BUTTONS OK.

Just an alternative approach when I got bored. Relying on an unverified error though is probably bad.

 

[tamhas]

I've always thought the easiest and most reliable way to get the last day of the month was to get the first day of the next month and subtract one.

 

[KnutHandsome]

I've always thought the easiest and most reliable way to get the last day of the month was to get the first day of the next month and subtract one.

I'm sure it is, though I must admit, until I saw Tom's code, I'd forgotten about it.

Speaking of which, for some reason when I read this comment:

/* the last day of the month before the month after the month in question */

I couldn't help thinking of Gilbert and Sullivan. I was actually singing the damn thing in my head.

 

[KnutHandsome]

I was actually singing the damn thing in my head.

Probably something I should keep to myself, eh?

 

[TomBascom]

It's nice to see my effort didn't go to waste

 

[MrMacaroon]

I am with tamhas on this one. That is how I do it. Now finding the week of the year is a little harder ;-)

Cameron