Convert Alphanumeric to Date / DATEPATTERN (SOLVED)
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)
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...
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)
Prod/Dev: WebFOCUS 8.0.08 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.08 on Windows 7 Pro
September 21, 2012, 07:25 PM
Dan Satchell
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.
WebFOCUS 7.7.05
September 21, 2012, 07:56 PM
Joey Sandoval
I tried changing the usage value to A6YYM, but I received an error. I was only able to save it as YYM.
Prod/Dev: WebFOCUS 8.0.08 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.08 on Windows 7 Pro
September 21, 2012, 09:15 PM
Joey Sandoval
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,
Prod/Dev: WebFOCUS 8.0.08 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.08 on Windows 7 Pro
September 24, 2012, 09:04 AM
jgelona
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?
In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
September 27, 2012, 09:47 AM
Joey Sandoval
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.
Prod/Dev: WebFOCUS 8.0.08 on Windows Server 2008/Tomcat , WebFOCUS DevStudio 8.0.08 on Windows 7 Pro