Date minus (-) one month via formular
I look for a BW formula to calculate a date - one month. At the moment I use the following formula but I'm not happy with this solution.
IF( DATE_MONTH( CALDAY ) = DATE_MONTH( ADD_TO_DATE( LOAD_DATE, NEGATIVE( 15 ) ) ), WS_COUNT, 0 )
Brian Anthony replied
Here's a go at it using a routine. Not sure if the c's need to be i's for the calculations, so you may need to play around with it. Also this code doesn't check for validity of the 29th, 30th, 31st dates (March 31 going back to Feb 31 is invalid).
DATA: l_m1(2) type c,
l_m2(2) type c,
l_y(4) type c,
l_d(2) type c,
l_lastmonth like <date_field>.
l_y = <date_field>+0(4). "first 4 characters = year
l_m1 = <date_field>+4(2). "5-6 = month
l_d = <date_field>+6(2). "7-8 = day
l_m2 = l_m1 - 1. " get last month
IF l_m1 = 0. "check for january
l_m1 = 12.
l_y = l_y - 1.
CONCATENATE l_y l_m1 l_d into l_lastmonth. "rebuild date
RESULT = l_lastmonth.
It's a start and a direction, so take it as such and not as the correct solution because I don't really know your requirements or exactly what you are trying to do.