Hi All,

I have a calendar control in my HTML and i have defined its date format as trMYY (i.e September, 2011). This value is passed to my fex file on page sumbit and i am trying to convert this to a YYM format in order to use it as a filter. I have tried CHGDAT, DATECVT and EDIT. No luck yet. Here is what i am doing, can any one please help me out

&START_DT is the variable coming from Calendar control. The value will be 'September, 2011'

-SET &START_DT = TRUNCATE(&START_DT);
-SET &ST_MON = GETTOK(&START_DT,&START_DT.LENGTH,1,',',9,'A9');
-SET &ST_MON = UPCASV(9, &ST_MON, 'A9');
-SET &ST_YR = GETTOK(&START_DT,&START_DT.LENGTH,2,',',9,'A9');
-SET &START_DT = TRUNCATE(&ST_MON) | TRUNCATE(&ST_YR);
-SET &ST_DT = CHGDAT('MXYY','A6YYM','&START_DT','A6');
-TYPE &ST_DT

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

Alas, an ampersand variable does not know date formats from adam. Ampersand Variables can be ALpha, or numeric, and their contents, AND their usage in the CURRENT Dialog Manager line of code determine how they will be evaluated.

In this case, I would use a DECODE of your &ST_MON variable to convert 'September' into '09'. Then hard concatente that onto &ST_YR as in &ST_YY || &ST_MON to create something theat looks liek a YYM date.

Now, create a DEFINE field using your CHGDAT function and use that defined field in your WHERE clause.

Thnks Robert. I was also thinking of using a Define field for my Where Clause. But that is converting my Hold file field to this trMYY format. Anyways just wanted to know if there is something available in dialog manager to convert trMYY to YYMD format. One more reason why i wanted it to be converted is to use this variable in my SQL Pass thro filter. I guess i have to the use your DECODE logic. Thanks much for your help.

I had the exact same issue with the date coming from a launch page. This is the logic I use to process the incoming date (&RPT_MONTH is the incoming date) and determine begin and end dates for WHERE...FROM...TO clauses in the report logic:

-SET &MONTH_NAME = UPCASE(9,GETTOK(&RPT_MONTH,&RPT_MONTH.LENGTH,1,',',9,'A9'),'A9');
-SET &MONTH_NBR  = DECODE &MONTH_NAME ('JANUARY'   '01'
-                                      'FEBRUARY'  '02'
-                                      'MARCH'     '03'
-                                      'APRIL'     '04'
-                                      'MAY'       '05'
-                                      'JUNE'      '06'
-                                      'JULY'      '07'
-                                      'AUGUST'    '08'
-                                      'SEPTEMBER' '09'
-                                      'OCTOBER'   '10'
-                                      'NOVEMBER'  '11'
-                                      'DECEMBER'  '12');
-SET &RPT_YEAR   = EDIT(GETTOK(&RPT_MONTH,&RPT_MONTH.LENGTH,2,',',5,'A5'),'$9999');
-SET &RPT_YR_MTH = &RPT_YEAR || &MONTH_NBR ;
-*
-SET &NXT_YR_MTH     = AYM(&RPT_YR_MTH,1,'I6YYM');
-SET &NXT_MTH_BEG    = &NXT_YR_MTH | '01';
-SET &RPT_MTH_BEG    = &RPT_YR_MTH | '01';
-SET &RPT_MTH_END    = AYMD(&NXT_MTH_BEG,-1,'I8');
-SET &RPT_YEAR_BEG   = &RPT_YEAR | '0101';
-SET &RPT_YEAR_END   = &RPT_YEAR | '1231';