Hi I am having a date input in alphanumeric(A9)format like 15-Oct-07 but in my report O/P i need to diplay like 10/15/2007.
I tried the below code and it works fine.But when i give my field name it is not working.

DEFINE FILE CAR
TEST_DATE/A9='15-Oct-07';
Get_Month/A3=EDIT(TEST_DATE,'$$$999');
Month/A2= DECODE Get_Month ('Jan' '01' 'Feb' '02' 'Mar' '03' 'Apr' '04' 'May' '05' 'Jun' '06'
'Jul' '07' 'Aug' '08' 'Sep' '09' 'Oct' '10' 'Nov' '11' 'Dec' '12' ELSE ' ');
Day/A2=EDIT(TEST_DATE,'99');
Year/A2=EDIT(TEST_DATE,'$$$$$$$99');
ALPHA_DATE/A8MDYY = Month | Day |'20'| Year;
FINAL_DATE/MDYY = ALPHA_DATE;
END

TABLE FILE CAR
PRINT COUNTRY TEST_DATE Month Day Year ALPHA_DATE FINAL_DATE
END
-RUN
-EXIT

Is it a column from a table or is it a Dialogue Manager variable?

If it's a column of a table, what error do you get? Perhaps the size of the column is larger than 9 characters. If so, use EDIT or SUBSTR to extract the first 9 characters.

If it's a Dialogue Manager variable, this should work:

-SET &DATE1 = '15-Oct-07';

DEFINE FILE CAR
TEST_DATE/A9='&DATE1';
GET_MONTH/A3=EDIT(TEST_DATE,'$$$999');
MONTH/A2= DECODE Get_Month ('Jan' '01' 'Feb' '02' 'Mar' '03' 'Apr' '04' 'May' '05' 'Jun' '06'
'Jul' '07' 'Aug' '08' 'Sep' '09' 'Oct' '10' 'Nov' '11' 'Dec' '12' ELSE ' ');
DAY/A2=EDIT(TEST_DATE,'99');
YEAR/A2=EDIT(TEST_DATE,'$$$$$$$99');
ALPHA_DATE/A8MDYY = Month | Day |'20'| Year;
FINAL_DATE/MDYY = ALPHA_DATE;
END

TABLE FILE CAR
PRINT
COUNTRY NOPRINT
TEST_DATE MONTH DAY YEAR ALPHA_DATE FINAL_DATE
END
-RUN

What error do you get exactly?

Actually it is a column read from external CSV file.In the file it is in 1-Oct-07,15-Oct-07 etc etc.. format.So inorder to read it i have used A9 in master file.

DEFINE FILE ONE
TEST_DATE/A9=TDate;
Get_Month/A3=EDIT(TEST_DATE,'$$$999');
Month/A2= DECODE Get_Month ('Jan' '01' 'Feb' '02' 'Mar' '03' 'Apr' '04' 'May' '05' 'Jun' '06'
'Jul' '07' 'Aug' '08' 'Sep' '09' 'Oct' '10' 'Nov' '11' 'Dec' '12' ELSE ' ');
Day/A2=EDIT(TEST_DATE,'99');
Year/A2=EDIT(TEST_DATE,'$$$$$$$99');
ALPHA_DATE/A8MDYY = Month | Day |'20'| Year;
FINAL_DATE/MDYY = ALPHA_DATE;
END

TABLE FILE ONE
PRINT
Get_Month
Month
Day
Year
ALPHA_DATE
FINAL_DATE
BY ASSET_ID
END
-RUN
-EXIT

:::::error:::::
0 ERROR AT OR NEAR LINE 353 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TDate
0 ERROR AT OR NEAR LINE 355 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TEST_DATE
0 ERROR AT OR NEAR LINE 357 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: Get_Month
0 ERROR AT OR NEAR LINE 363 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: TEST_DATE
0 ERROR AT OR NEAR LINE 365 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: Month
0 ERROR AT OR NEAR LINE 367 IN PROCEDURE MEMFEX FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: ALPHA_DATE
0 ERROR AT OR NEAR LINE 405 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: Get_Month
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Ali - is this you? I recognize this code. Send me your folder and focexec and I'll take a look

Carol

Sure Carol yes its me

Check your Master. It appears that file ONE does not have a field named TDate.

TDate field is present both in MASTER and the ONE file.

Could you post your master file for ONE? About the only explanation for the error you're getting is the one offered by Francis. Possibly you have more than one copy of it in your server path.

Umar (or Ali?),

If your date is coming in as 1-Oct-07 or 15-Oct-07 then a fixed length field substring is not going to help you one bit (Get_Month/A3=EDIT(TEST_DATE,'$$$999')) as in the first date the value for Get_Month would be "ct-" and the second would be OK.

Try using GETTOK for the components. I would have suggested using STRREP to change the hyphens to slashes but STRREP objects to the '-' as a parameter as either change string.

-SET &Date = '1-Oct-07';
-SET &Umar_Day1 = GETTOK(&Date, 9, 1, '-', 2, 'A2');
-SET &Umar_Mth1 = GETTOK(&Date, 9, 2, '-', 3, 'A3');
-SET &Umar_Yer1 = GETTOK(&Date, 9, 3, '-', 2, 'A2');
-SET &Date = '15-Oct-07';
-SET &Umar_Day2 = GETTOK(&Date, 9, 1, '-', 2, 'A2');
-SET &Umar_Mth2 = GETTOK(&Date, 9, 2, '-', 3, 'A3');
-SET &Umar_Yer2 = GETTOK(&Date, 9, 3, '-', 2, 'A2');
-? &Umar

T

Sadly, the function CHGDAT cannot convert from 03-OCT-07 to 20071003, just the other way around. It would have made your conversion a lot simpler.

Who or what creates the flat file?

Is it a possibility to go back to the source and let them create the flat file with

20071015
20071001
etc

Thanks Very Much Tony
GETTOK worked fine.

Thanks a TON!!

Its an extract from another system

Fixed length it is not, so standard editing would not work here. Also as pointed out by Tony using a '-' is not a recommended practice b/c given the wrong scenario it could be misunderstood to be a minus sign in a calculation.
Fixed format would be the best case to work with. Tony's suggestion right on the mark.

Ira WF 5.3.6 (aix)

Umar

You might be able to do the extract from the other system with WF.
If that field is a real date field in the database you would be in charge and be able to convert it at the extraction moment.

Just a thought.

I am not incharge of the extraction process. I just get the CSV file and i start working on it.Can make this as a suggestion to other members incharge of making the extract from that system.It will also be helpfull in future.

Thanks for your thought.

Umar,

I am glad that you have managed to progress your problem.

T

I wonder if someone can explain the following. If you do the following, it works:

DEFINE FILE XXX
DATE1/MDYY='15 OCT 07';
END

But this doesn't work.

Also, to get this to work, one has to make sure the DEFCENT and YRTHRESH are properly set.

DEFINE FILE XXX
DATE1/A9='15 OCT 07';
DATE2/MDYY=DATE1;
END

It seems if I use a date literal, I can convert the literal to a smart date. I don't have to do all the DECODE, Concatenation, etc. But if I use a field that contains a date literal, I cannot, unless I parse the components. I don't know about you guys, but this seems like a bug to me.

In Umar's problem, say TDATE contains '15-OCT-07', then the following should work.

TEST_DATE/A9=CTRAN(9,TDATE,45,32,'A9');
FINAL_DATE/MDYY=TEST_DATE;

Seems to me, that if TEST_DATE contains '15 OCT 07', there should be no difference between following:

FDATE1/MDYY='15 OCT 07';
FDATE2/MDYY=TEST_DATE;

yet FDATE1 will work while FDATE2 fails.

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

I too wonder when date is directly passed it works.But when a field containing date is passed it doesn't.