Here's a good one for you!

I need to be able to add x number of months to a complete date (YYYYMMDD) in Dialog Manager but the only function I've found that does that is AYM but it only uses a date in Year-Month format...

I've done this:

-SET &TODAYYM = EDIT(&YYMD,'999999');
-SET &EXPDATEYYM = AYM(&TODAYYM, +6, 'I6');
-SET &EXPDATE =
&EXPDATEYYM || EDIT &YYMD,'$$$$$$99');

But I'll have a problem if the current day doesn't exist in the new month ex: 20050331 + 6 months will give me 20050931 but September only have 30 days and my resulting date will be invalid.

I'm claculating that date to use it in the WHERE CLAUSE of an SQL statement on a MSSQL server via WebFocus. That's why it needs to be calculated in Dialog Manager.

Any idea??

Please

Thanks for your help

Hi Flipper

Try the following... just truncate to the first of the month, add the months and then add the correct number of days. Like this... gives 20051002 for 20050331+6 months.

-SET &DATE1='20050331';
-SET &DAYOFMONTH=EDIT(&DATE1, '$$$$$$99');
-SET &DATETEMP=EDIT(&DATE1, '999999');
-SET &DATE2=AYM(&DATETEMP,6,'I6');
-SET &DATE3=&DATE2 | '01';
-SET &DATE_FINAL=AYMD(&DATE3,&DAYOFMONTH,'YYMD');
-TYPE &DATE1
-TYPE &DATE_FINAL

Is this what you need?

Jodye

Thank you sooooo much. That's exactly what I was looking for. In fact, you must substract 1 day to the nb of days you are adding back to get exactly the desired date i.e. 20050331 + 6 months = 20051001.

Happy Easter!

Hi again

Correction. Here is a better way. Essentially you just add the 6 months then test the date. If its not valid then remove one day and test it again. So 20050331 plus 6 months results in 20050930.

Do this three times (max number of times the date could be invalid ... FEB29 FEB30 FEB31.) Could put that in a loop but I didnt bother. Am I missing something people? Is there an easier way to do this?

-SET &DATE1='20050831';
-TYPE ORIGINAL DATE = &DATE1
-SET &DATETEMP=EDIT(&DATE1, '999999');
-SET &DATE2=AYM(&DATETEMP,6,'I6');
-SET &DAYOFMONTH=EDIT(&DATE1, '$$$$$$99');
-SET &DATENOW=&DATE2 | &DAYOFMONTH;
-TYPE DATENOW = &DATENOW
-*TEST TO SEE IF VALID
-SET &TESTDATE = DATECVT(&DATENOW , 'I8YYMD' , 'YYMD');
-IF &TESTDATE NE 0 GOTO ALLDONE;
-SET &DAYOFMONTH = &DAYOFMONTH -1;
-SET &DATENOW=&DATE2 | &DAYOFMONTH;
-TYPE DATENOW = &DATENOW
-*TEST TO SEE IF VALID
-SET &TESTDATE = DATECVT(&DATENOW , 'I8YYMD' , 'YYMD');
-IF &TESTDATE NE 0 GOTO ALLDONE;
-SET &DAYOFMONTH = &DAYOFMONTH -1;
-SET &DATENOW=&DATE2 | &DAYOFMONTH;
-TYPE DATENOW = &DATENOW
-*TEST TO SEE IF VALID
-SET &TESTDATE = DATECVT(&DATENOW , 'I8YYMD' , 'YYMD');
-IF &TESTDATE NE 0 GOTO ALLDONE;
-SET &DAYOFMONTH = &DAYOFMONTH -1;
-SET &DATENOW=&DATE2 | &DAYOFMONTH;
-TYPE DATENOW = &DATENOW
-ALLDONE
-TYPE FINAL DATE IS &DATENOW

Thats not good either (I mean the original solution minus one day)... try this...

-SET &DATE1='20050830';
-SET &DAYOFMONTH=EDIT(&DATE1, '$$$$$$99');
-SET &DAYOFMONTH=&DAYOFMONTH-1;
-SET &DATETEMP=EDIT(&DATE1, '999999');
-SET &DATE2=AYM(&DATETEMP,6,'I6');
-SET &DATE3=&DATE2 | '01';
-SET &DATE_FINAL=AYMD(&DATE3,&DAYOFMONTH,'YYMD');
-TYPE &DATE1
-TYPE &DATE_FINAL
-EXIT

you end up with 20060302.

Have you tried using the DATEMOV and DATEADD functions?

-SET &DT = AYMD(&YYMD,-1,'I8');
-SET &TIME_SPAN = 6;
-SET &TIME_DURA = 1;

-SET &START_DATE = DATECVT
((DATEMOV(DATEADD((DATECVT(&DT,'I8YYMD','YYMD')),'M',&TIME_SPAN),'BOM')),
'YYMD','I8YYMD');

-SET &END_DATE = DATECVT((DATEMOV(DATEADD((DATECVT(&START_DATE,'I8YYMD','YYMD')),
'M',&TIME_DURA),'EOM')),'YYMD','I8YYMD');

This message has been edited. Last edited by: <Mabel>,

Hey Jodye, If you put the number of days minus 1 in another variable, it works... I don't know why.

-SET &DATE1='20050331';
-SET &DAYOFMONTH=EDIT(&DATE1, '$$$$$$99');
-SET &NBDAYS = &DAYOFMONTH - 1;
-SET &DATETEMP=EDIT(&DATE1, '999999');
-SET &DATE2=AYM(&DATETEMP,6,'I6');
-SET &DATE3=&DATE2 | '01';
-SET &DATE_FINAL=AYMD(&DATE3,&NBDAYS,'YYMD');
-TYPE &DATE1
-TYPE &DATE_FINAL
-EXIT

Results:
20050331
20051001


CusrtisA, thanks for the solution, I thought that DATECVT, DATEMOV and DATEADD functions didn't work in Dialog Manager?

I have had no problems using those functions in DM.

Well, here's what they say in the help: Do not use DATEADD with Dialogue Manager. DATEADD requires a date to be in date format; Dialogue Manager interprets a date as alphanumeric or numeric.

It's working because you use DATEADD with DATECVT to convert the alphanumeric date into a real date. Good idea!

I'll stick with Jodye's idea because when my start date = 2005.03.31, I want the result of adding 6 months to be 2005.10.01.

-SET &DATE1='20050331';
-SET &DAYOFMONTH=EDIT(&DATE1, '$$$$$$99');
-SET &NBDAYS = &DAYOFMONTH - 1;
-SET &DATETEMP=EDIT(&DATE1, '999999');
-SET &DATE2=AYM(&DATETEMP,6,'I6');
-SET &DATE3=&DATE2 | '01';
-SET &DATE_FINAL=AYMD(&DATE3,&NBDAYS,'YYMD');
-TYPE &DATE1
-TYPE &DATE_FINAL
-EXIT

When using DATEADD, If the calculation using the month unit creates an invalid date, DATEADD corrects it to the last day of the month. And That's not what I want.

It's just because I'm converting an SAP/ABAP report into a Web Focus one and the function they used was calculating it like that. Since I want the new report to have the same results, I have no choice.

Thanks to everybody for the suggestions.

Hi Flipper

Try that code with the original date=20050830. You get a result of 20060302. Thats not what you want.

Correct me if Im wrong. You want to add 6 months. Then if the result is not a valid date (like FEB 30) then move the result to the first of the NEXT month. Right?

This will work under all circumstances:

-SET &DATE1='20050830';
-TYPE ORIGINAL DATE = &DATE1
-SET &DATETEMP=EDIT(&DATE1, '999999');
-SET &DATE2=AYM(&DATETEMP,6,'I6');
-SET &DAYOFMONTH=EDIT(&DATE1, '$$$$$$99');
-SET &DATENOW=&DATE2 | &DAYOFMONTH;
-TYPE DATENOW = &DATENOW
-SET &TESTDATE = DATECVT(&DATENOW , 'I8YYMD' , 'YYMD');
-IF &TESTDATE NE 0 GOTO ALLDONE;
-*MOVE TO FIRST OF CURRENT MONTH
-SET &DATETEMP=EDIT(&DATENOW, '999999')|'01';
-TYPE &DATETEMP
-*ADD ONE MONTH
-SET &DATENOW=DATECVT((DATEADD((DATECVT (&DATETEMP, 'I8YYMD', 'YYMD')),'M', 1)), 'YYMD','I8YYMD');
-ALLDONE
-TYPE FINAL DATE IS &DATENOW
-EXIT