Can anyone show me how to convert an alphanumeric value, such as '201208', to a legacy date format 'I6YYM'?

I have tried several different ways to no avail. For example, I thought it would be as easy as this.. (I have only been using WebFocus for about 1 month so please bare with me)

 
TEST_YEAR/YY = '2012';
TEST_MONTH/M = '02';
TEST_YEARMONTH_FROMALPHA/I6YYM = TEST_YEAR | TEST_MONTH; 

Background to my Problem:

I am reading and writing from/to a SQL data source. I have a (YYYYMM) date field which is stored in SQL as an alphanumeric format "varchar(20)" or "A20V" in Focus. I had success at defining my master file with the DATEPATTERN attribute so that WebFocus would automatically convert Alphanumeric data to YYM dates when READING from my datasource. However, now I have an issue when I WRITE data to my SQL table. For example using the modify file function, I am trying to send "201201" as an Alphanumeric... but WebFocus is converting this value and storing it as '1333' in my SQL DB. I know this has something to do with my USAGE attribute = 'YYM'. Therefore, I think if I can convert my alphanumeric input to I6YYM before I pass to SQL , the masterfile should somehow convert this back to Alphanumeric for me...

 
FIELDNAME=ACCOUNTSTARTDATE, ALIAS=ACCOUNTSTARTDATE, DEFCENT=20, USAGE=YYM, ACTUAL=A20V, 
DATEPATTERN='[YYYY][MM]', $ 

Any help would be greatly appreciated.

Thank You,

This message has been edited. Last edited by: Joey Sandoval,

Try this (the concatenation character between the YY and M in the format may not be necessary):

TEST_YEARMONTH_FROMALPHA/A6YY|M = ACCOUNTSTARTDATE ;

No I tried this code:

 
TEST_DATE/I6YY|M = '201202';
 

I receive the following error: "NUMERIC ARGUMENTS IN PLACE WHERE ALPHA ARE CALLED FOR"..

Either of these should work, but I thought you wanted to convert ACCOUNTSTARTDATE?

TEST_DATE/A6YYM = '201202';
TEST_DATE/I6YYM = 201202 ;

Dan, the A6YYM format worked ..which is good .. but I still have problem when the ACCOUNTSTARTDATE value is passed to SQL.


I am setting "201201" as the ACCOUNTSTARTDATE and passing it to SQL using the modify command. However in my SQL DB it is storing "1333". I think there is something wrong with my master file. Any ideas? (Below is the line from my master)

 FIELDNAME=ACCOUNTSTARTDATE, ALIAS=ACCOUNTSTARTDATE, DEFCENT=20, USAGE=YYM, ACTUAL=A20V, DATEPATTERN='[YYYY][MM]', $
 

You could try changing the USAGE for ACCOUNTSTARTDATE from YYM to A6YYM. I've never seen the DATEPATTERN parameter before and am unfamiliar with its function.

I tried changing the usage value to A6YYM, but I received an error. I was only able to save it as YYM.

Dan I appreciate your help. I ended up removing the DATEPATTERN attribute completely and set both the USAGE and ACTUAL ones to A20 to keep everything consistent.

I then used the format notations you mentioned in defines, when I need the alphanumeric values in a date format.

  
ACCOUNTSTARTDATE_DT/A6YY|M = ACCOUNTSTARTDATE;

Thank you,

Seems to me you are over thinking this and you all need is the EDIT function

FIELDNAME=ACCOUNTSTARTDATE, ALIAS=ACCOUNTSTARTDATE, USAGE=A20, ACTUAL=A20,$

TESTDATE/I6YYM=EDIT(EDIT(ACCOUNTSTARTDATE,'999999'));

then when going back to save for MODIFY

ACCOUNTSTARTDATE/A20=EDIT(TESTDATE);

The first EDIT gets the first 6 characters of the string and the 2nd EDIT converts the alpha string to a number. So a string '201202' is converted to a number 201202.

Is that what you need?

Yes thank you jgelona. I was not understanding theformats correctly, and I was also trying to take advantage of the DATEPATTERN Attribute in the masterfile which was also causing issues.

There is a way to do this. In the MFD:

.
.
    FIELDNAME=U_ACCOUNTSTARTDATE, ALIAS= ACCOUNTSTARTDATE, USAGE=A20V, ACTUAL=A20V,
    ACCESS_PROPERTY=(INTERNAL), $
    FIELDNAME=ACCOUNTSTARTDATE, ALIAS= ACCOUNTSTARTDATE, USAGE=YYM, ACTUAL=A20V, 
    DATEPATTERN='[YYYY][MM]',$
.
.

As long as you update only U_ACCOUNTSTARTDATE and read ACCOUNTSTARTDATE everything will be fine.

U_ACCOUNTSTARTDATE will not be displayed in the WebFOCUS tools because of the ACCESS_PROPERTY.

DATEPATTERN is a good attribute for all data files except FOC and XFOC, and can overcome many non-standard date based formats.