I need to build a new DEFINE field in my MAS file that is the number of months until a credit card expires. In the data now I have two I11 fields, CREDITCARDEXPMO and CREDITCARDEXPYR (integer month and year).

So far I have been able to build a composite expiration date (A10V) field as shown below:

EDIT(FPRINT(CREDITCARDEXPMO, 'I11', 'A11'),'$$$$$$$$$99') | '/01/' | EDIT(FPRINT(CREDITCARDEXPYR, 'I11', 'A11'),'$$$$$$$9999') 

Now I need to convert this from A10V to YYMD, however DATECNV(^that expression above^, 'A10V','YYMD') only return blanks which makes me believe there is an error above.

Ideas or alternative methods to accomplish this are most welcome.

This message has been edited. Last edited by: Kerry,

Try this:

CC_EXPIRE/A8YYMD = EDIT(FPRINT(CREDITCARDEXPYR, 'I11', 'A11'),'$$$$$$$9999') | EDIT(FPRINT(CREDITCARDEXPMO, 'I11', 'A11'),'$$$$$$$$$99') | '01';
CARD_EXPIRE/YYMD = CC_EXPIRE ;

This message has been edited. Last edited by: Dan Satchell,

So far so ... strange.

I have implemented your suggestion and see valid dates in the CARD_EXPIRE field (YYMD) such as 2010/12/01 and 2012/11/01.

Then in my final step I added another DEFINE (I4) as:

 
DATEDIF(&YYMD, CARD_EXPIRE, 'M') 

And my resulting integer values appear to be 1319 to 1342 respectively.

Any idea why my months difference seem so high?

Try putting single quotes around &YYMD:

DATEDIF('&YYMD', CARD_EXPIRE, 'M');

ewinery, in order to make use of WebFOCUS date functions (DATEDIF, DATEMOV, DATEADD, etc.) you have to convert your &YYMD value (which today looks like 20111219 and is sort of equivalent to what FOCUS calls a SmartDate) and convert it to a "date" as WebFOCUS expects.

Try:

DATEDIF(DATECVT('&YYMD', 'A8YYMD', 'YYMD'), CARD_EXPIRE, 'M') 

That would probably give you what you need.

quote:

DATEDIF(DATECVT('&YYMD', 'A8YYMD', 'YYMD'), CARD_EXPIRE, 'M')

That does the trick. Thanks.

Another solution using the CAR file.
I created 2 fields for Month and Year: EXPMO EXPYR.
Since your fields are already numbers, a bit of arithmetic is in order instead of converting to alpha (EXPDT). Then a simple assignment converts to a FOCUS date (EXPMY), after which a substraction provides the result (MONTHS). Use &DATEMYY for today's date:

  
DEFINE FILE CAR
EXPMO/I2=1 + IMOD( DEALER_COST, 12, 'I2');
EXPYR/I4=2012 + IMOD(RETAIL_COST, 10, 'I2');
EXPDT/I6MYY=EXPMO*10000 + EXPYR;
EXPMY/MYY=EXPDT;
TDY/MYY='&DATEMYY';
MONTHS/I4=EXPMY - TDY;
END
TABLE FILE CAR
PRINT 
     CAR
     EXPMO
     EXPYR
     EXPMY
     TDY
     MONTHS
END