Calculate End-Of-Month (EOM)

, ,
1

Here is an example to calculate EOM of the current or prior month:

* EOM: End of Month                      
DEFINE DATA                              
LOCAL                                    
1 #DATE (D) INIT <*DATX>                  
1 #DATEA (A8)                            
1 REDEFINE #DATEA                        
  2 YYYY (N4)                            
  2 MM (N2)                              
  2 DD (N2)                              
END-DEFINE                                
INPUT (AD=M) #DATE                        
ADD 31 TO #DATE /* for current EOM only, otherwise prior EOM 
MOVE EDITED #DATE (EM=YYYYMMDD) TO #DATEA 
#DATEA.DD := 1   /* first of current month                        
MOVE EDITED #DATEA TO #DATE (EM=YYYYMMDD) 
SUBTRACT 1 FROM #DATE    /* end of prior month                
PRINT 'EOM:' #DATE                        
END

if you omit the ADD 31 TO #DATE you will get the EOM of the prior month, else you will get the EOM of the current month. Thus you are enabled to go to any EOM in the past or the future.

2

I suggest that you add 28 instead of 31, otherwise a date of 1/29/06 results in an EOM value of 2/28/06.

Actually, here’s the coe that I’ve used for years.

DEFINE DATA LOCAL                                                 
1 #DATE (D)   INIT <D'01/28/2006'>                                
1 #T (A8)     1 REDEFINE #T            /* Temp date               
  2 #DD (N2)                                                      
1 #D (D)                               /* For calculations        
END-DEFINE                                                        
*                                                                 
INPUT (AD=M) #DATE                                                
MOVE EDITED #DATE (EM=DDMMYYYY) TO #T                             
IF  #DD < 28                                                      
    ASSIGN #DD = 28                    /* Every month has 28      
END-IF                                                            
REPEAT UNTIL #DD = 1                   /* 'Search' for next month 
  MOVE EDITED #T TO #D (EM=DDMMYYYY)   /* Convert to D format     
  ADD 1 TO #D                                                     
  MOVE EDITED #D (EM=DDMMYYYY) TO #T   /* Convert to alpha        
END-REPEAT                                                        
SUBTRACT 1 FROM #D                     /* Last day of given month 
PRINT 'EOM:' #DATE #D                                             
END

This works regardless of the number of days in the following month.

3

My approach is closer to Wilfred’s:


define data local
01 #date(d) init <d'2006-01-29'>
01 #D(D)
01 #DA(A8)
END-DEFINE
*
INPUT (AD=M) #DATE
* quick trip to start of month
MOVE EDITED #DATE (EM=YYYYMM'01') TO #DA 
MOVE EDITED #DA TO #D (EM=YYYYMMDD)
SUBTRACT 1 FROM #D
PRINT 'end of prior month' #D (EM=YYYY-MM-DD)
add 32 TO #D
MOVE EDITED #D (EM=YYYYMM'01') TO #DA
MOVE EDITED #DA  TO #D (EM=YYYYMMDD)
SUBTRACT 1 FROM #D
*
PRINT 'End of current month' #D  (EM=YYYY-MM-DD)
*
END

I just use the edit mask to supply the “day” value instead of a redefine / move.

4

Please remember: Zero is a valid value for a date-field! All of your programs get a runtime error if input-date = 0.

BTW: Here’s another solution :wink:

define data local
01 #d-in  (D)
01 #d-out (D)
01 #a8 (A8)
01 redefine #a8
  02 filler 6x
  02 #n2 (N2)
end-define
*
input #d-in
move edited #d-in (EM=YYYYMM) to #a8
for #n2 = 31 to 28 step -1
  if #a8 = MASK(YYYYMMDD)
    move edited #a8 to #d-out (EM=YYYYMMDD)
    escape bottom
  end-if
end-for
display #d-out
*
end
5

DEFINE DATA LOCAL
1 #CCYYMMDD (N8) INIT <20040201>
1 REDEFINE #CCYYMMDD
2 #CCYY (N4)
2 #MM (N2)
2 #DD (N2)
END-DEFINE
*
#DD := 31
IF #CCYYMMDD NE MASK (YYYYMMDD)
#DD := 30
IF #CCYYMMDD NE MASK (YYYYMMDD)
#DD := 29
IF #CCYYMMDD NE MASK (YYYYMMDD)
#DD := 28
END-IF
END-IF
END-IF
WRITE ‘=’ #CCYYMMDD
END

6

I have to leave the office in a few minutes, but thought I would contribute some code for a similar problem that might help with this problem. The task was to accept a date format variable and create a date format variable with the same year and month, but day = 1. The most efficient code I could develop (important since this code is executed 50 million, yes, million, times a day) was the following:

DEFINE DATA LOCAL
1 #DATE-IN (D)
1 #DATE-OUT (D)
1 #DAYS-ALPHA (A2)
1 REDEFINE #DAYS-ALPHA
2 #DAYS-NUMERIC (N2)
END-DEFINE
*
INCLUDE AATITLER
INCLUDE AASETC
*
MOVE *DATX TO #DATE-IN
WRITE 5T ‘=’ #DATE-IN /
*
MOVE EDITED #DATE-IN (EM=DD) TO #DAYS-ALPHA
COMPUTE #DATE-OUT = #DATE-IN - #DAYS-NUMERIC + 1
*
WRITE 5X ‘=’ #DATE-OUT
END

PAGE #   1                    DATE:    Aug 2, 2006
PROGRAM: DATE02               LIBRARY: INSIDE

#DATE-IN: 06-08-12

 #DATE-OUT: 06-08-01

This will actually be part of the lead article in the next issue of Inside Natural.

steve

7

Just got back in the office and see that no one has played with this. Since I needed to unwind from a frustrating day, I decided to use the code above to derive code to answer the specific request:

DEFINE DATA LOCAL
1 #DATE-IN (D)
1 #DATE-OUT (D)
1 #DAYS-ALPHA (A2)
1 REDEFINE #DAYS-ALPHA
2 #DAYS-NUMERIC (N2)
END-DEFINE
*
INCLUDE AATITLER
INCLUDE AASETC
*
MOVE *DATX TO #DATE-IN
WRITE 5T ‘=’ #DATE-IN /
*
MOVE EDITED #DATE-IN (EM=DD) TO #DAYS-ALPHA
COMPUTE #DATE-OUT = #DATE-IN - #DAYS-NUMERIC
*
WRITE 5X ‘=’ #DATE-OUT 5X ‘END OF PREVIOUS MONTH’
*
COMPUTE #DATE-IN = #DATE-OUT + 32
MOVE EDITED #DATE-IN (EM=DD) TO #DAYS-ALPHA
COMPUTE #DATE-OUT = #DATE-IN - #DAYS-NUMERIC
WRITE 5X ‘=’ #DATE-OUT 5X ‘END OF CURRENT MONTH’
*
END

PAGE #   1                    DATE:    Sep 14, 2006
PROGRAM: DATE06               LIBRARY: INSIDE

#DATE-IN: 06-09-14

 #DATE-OUT: 06-08-31     END OF PREVIOUS MONTH
 #DATE-OUT: 06-09-30     END OF CURRENT MONTH

This solution has but one EXAMINE and one COMPUTE for the end of the previous month, and two COMPUTEs and one EXAMINE for the end of the current month.

True, it does not work for a value of zero, but so what? What is the end of the previous month if the date is zero?? :slight_smile:

8

Obviously, it is NAT1137 :wink:

It was just a hint, because I would make my modules as save as possible. From my point of view a date-field containting zero means “undefined”. So I would return “undefinded” in my EOM-calculation-module.

9

I took a slightly different approach…
Given a year and a month, this routine returns the # of days in the month.


DEFINE DATA 
PARAMETER                                     
1 YEAR (N4)                                               
1 MONTH (N2)                                              
1 DAYS (N2)                                               
LOCAL                                                     
1 #DAYS (N2/12) INIT <31,28,31,30,31,30,31,31,30,31,30,31>
1 #REM (N2)                                               
1 #Y (N4)                                                 
END-DEFINE                                                
*
DIVIDE 4 INTO YEAR GIVING #Y REMAINDER #REM  
IF MONTH = 2            
    AND #REM = 0        
  DAYS := 29            
ELSE                    
  DAYS := #DAYS(MONTH)  
END-IF                  
END
10

You are not accounting for the full definition of leap year. Years divisible by 100 are not leap years unless they are divisible by 400.

Better version:

DEFINE DATA
PARAMETER                                     
1 YEAR (N4)                                               
1 MONTH (N2)                                             
1 DAYS (N2)                                               
LOCAL                                                     
1 #DAYS (N2/12) INIT <31,28,31,30,31,30,31,31,30,31,30,31>
1 #REM-4 (N2)
1 #REN-100 (N2)
1 #REM-400 (N2)
1 #Y (N4)                                                 
END-DEFINE                                               
*
DAYS := #DAYS(MONTH) 
IF MONTH = 2           
  DIVIDE 400 INTO YEAR GIVING #Y REMAINDER #REM-400
  IF #REM-400 = 0
      ADD 1 TO DAYS
  ELSE
      DIVIDE 100 INTO YEAR GIVING #Y REMAINDER #REM-100
      IF #REM-100 NE 0
         DIVIDE 4 INTO YEAR GIVING #Y REMAINDER #REM-4
         IF #REM-4 = 0
            ADD 1 TO DAYS
         END-IF
      END-IF
END-IF                 
END
11

When I posted my Calendar routine, there was quite a number of enhancements proposed (different languages, different week starting days, etc). One very useful enhancement was Matthias’s routine for testing if a given year was a leap year. The idea is to let Natural do the work using an edit mask instead of the program doing multiple divides and looking at remainders.

Herewith a timing comparison between using multiple divides and an edit mask for this year. I have commented out (IGNORE) code that would be in common. To derive a true comparison, the FOR loop times should be subtracted from the other two loop times.

DEFINE DATA
LOCAL
1 YEAR (N4) INIT <2007>
1 MONTH (N2) INIT <2>
1 DAYS (N2)
1 #DAYS (N2/12) INIT <31,28,31,30,31,30,31,31,30,31,30,31>
1 #REM-4 (N2)
1 #REM-100 (N2)
1 #REM-400 (N2)
1 #Y (N4)
1 #CPU-ELAPSED (I4)
1 #CPU-TIME (I4)
1 #LOOP (I4)
1 #TESTYEAR (N8) INIT <00000229>
1 REDEFINE #TESTYEAR
2 #TESTER (N4)
1 REDEFINE #TESTYEAR
2 #TESTYEARA (A8)
END-DEFINE
*
INCLUDE AASETC
INCLUDE AATITLER
*
MOVE CPU-TIME TO #CPU-TIME
SETA. SETTIME
FOR #LOOP = 1 TO 100000
DIVIDE 400 INTO YEAR GIVING #Y REMAINDER #REM-400
IF #REM-400 = 0
IGNORE / ADD 1 TO DAYS
ELSE
DIVIDE 100 INTO YEAR GIVING #Y REMAINDER #REM-100
IF #REM-100 NE 0
DIVIDE 4 INTO YEAR GIVING #Y REMAINDER #REM-4
IF #REM-4 = 0
IGNORE /* ADD 1 TO DAYS
END-IF
END-IF
END-IF
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-TIME
WRITE 5T ‘DIVIDE TIMES:’ *TIMD (SETA.) #CPU-ELAPSED
*
MOVE CPU-TIME TO #CPU-TIME
SETB. SETTIME
FOR #LOOP = 1 TO 100000
MOVE YEAR TO #TESTER
IF #TESTYEARA = MASK (YYYYMMDD)
IGNORE / ADD 1 TO DAYS
END-IF
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-TIME
WRITE 5T ‘MASK TIMES:’ *TIMD (SETB.) #CPU-ELAPSED
*
MOVE *CPU-TIME TO #CPU-TIME
SETC. SETTIME
FOR #LOOP = 1 TO 100000
IGNORE
END-FOR
COMPUTE #CPU-ELAPSED = *CPU-TIME - #CPU-TIME
WRITE 5T ‘FOR LOOP TIMES:’ *TIMD (SETC.) #CPU-ELAPSED
END

PAGE #   1                    DATE:    Mar 30, 2007
PROGRAM: CALTIM01             LIBRARY: SYSTEM

DIVIDE TIMES:       41         411
MASK TIMES:        8          75
FOR LOOP TIMES:        2          19

Note, we are not talking small numbers. After the subtraction of the FOR loop, the elapsed times show a ratio of over six to one, while the CPU ration is seven to one. In addition, although such observations are indeed subjective, I think the IF…MASK test is easier to read.

steve

12

It’s also faster on Natural@Windows
DIVIDE TIMES: 13 128
MASK TIMES: 2 24
FOR LOOP TIMES: 1 4

BTW: Your code gets more readable if you enclose it in code-tags. Like that:

[code]IF 1 = 1
  IGNORE
ELSE
  IGNORE
END-IF[/code]
13

Okay, I figured I’d chime in with my method for finding EOM…

01 #DATE (A8)
01 REDEFINE #DATE
02 #DATE-YYYYMM (A6)
02 #DATE-DD (N2)
*

  • POPULATE #DATE WITH A DATE FROM A
  • MONTH YOU WANT TO FIND THE END OF…

MOVE 31 TO #DATE-DD
REPEAT UNTIL #DATE = MASK(YYYYMMDD)
SUBTRACT 1 FROM #DATE-DD
END-REPEAT
*

  • #DATE NOW CONTAINS THE LAST VALID DATE OF THE MONTH
14

Okay, I figured I’d chime in with my method for finding EOM…

01 #DATE (A8)
01 REDEFINE #DATE
02 #DATE-YYYYMM (A6)
02 #DATE-DD (N2)
*

  • POPULATE #DATE WITH A DATE FROM A
  • MONTH YOU WANT TO FIND THE END OF…

MOVE 31 TO #DATE-DD
REPEAT UNTIL #DATE = MASK(YYYYMMDD)
SUBTRACT 1 FROM #DATE-DD
END-REPEAT
*

  • #DATE NOW CONTAINS THE LAST VALID DATE OF THE MONTH :smiley:
15

1 #DATE (A8)
1 REDEFINE #DATE
2 #DATE-YYYY (N4)
2 #DATE-MM (N2)
2 #DATE-DD (N2)
1 #DATE-D (D)

#DATE-D := *DATX

…

MOVE EDITED #DATE-D (EM=YYYYMMDD) TO #DATE
IF #DATE-MM = 12 THEN
#DATE-DD := 31
MOVE EDITED #DATE TO #DATE-D (EM=YYYYMMDD)
ELSE
ADD 1 TO #DATE-MM
#DATE-DD := 1
MOVE EDITED #DATE TO #DATE-D (EM=YYYYMMDD)
SUBTRACT 1 FROM #DATE-D
MOVE EDITED #DATE (EM=YYYYMMDD) TO #DATE-D
END-IF

16

Yep … as always steves solution is the cleanest