Hi All,

I have a Date values in A9V format in Oracle DB. Eg, Jul, 2012, Aug, 2012, Sep, 2012, Oct, 2012.
I'd like to convert to DATE formats(YYM or MYY), Is there any easy way or function available for this?

I guess, we can achieve this by using

SUBSTR function for extracting the strings(Jul,Aug, etc) and YEAR(2012,2013 etc), followed by IF.. THEN..ELSE and concatenating two fields, assigned to desire DATE format.

Something like below,

 
 -* Note, it doesn't have whole code
-*EFFECTIVE_DATE  = Jul, 2012, Aug, 2012, Sep, 2012, Oct, 2012 etc
DEFINE FILE TEST
DALPHAFMT/A10 = EFFECTIVE_DATE;
STR1/A3 = SUBSTR(10, DALPHAFMT, 1, 3, 3, STR1);
STR2/A5 = SUBSTR(10, DALPHAFMT, 5, 9, 5, STR2);
STR3/A10 = STR1 | STR2;
STR4/A4 = IF STR1 EQ 'Jan' THEN '01' ELSE IF STR1 EQ 'Feb' THEN '02' ELSE IF STR1 EQ 'Mar' THEN '03' ELSE IF STR1 EQ 'Apr' THEN '04' ELSE IF STR1 EQ 'May' THEN '05' ELSE IF STR1 EQ 'Jun' THEN '06' ELSE IF STR1 EQ 'Jul' THEN '07' ELSE IF STR1 EQ 'Aug' THEN '08' ELSE IF STR1 EQ 'Sep' THEN '09' ELSE IF STR1 EQ 'Oct' THEN '10' ELSE IF STR1 EQ 'Nov' THEN '11' ELSE '12';
END

TABLE FILE TEST
PRINT 
EFFECTIVE_DATE
STR1
STR2
STR3
STR4
END

But, seems its not efficient to my knowledge.

Please, help me on this.

Thanks,
Rifaz

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

Repeating this again:

In any data source excluding .foc or .xfoc, the ability to convert alpha strings to dates directly within the MFD exists by using DATEPATTERN.

. . . .USAGE=YYM,ACTUAL=A9V,DATEPATTERN='[Mon], [YYYY]',$

Really saves a lot of work!

Yes, you had posted this before, and its a great one, I'm bookmarking this page.

The benefits of a search....

Thanks Alan,

Believe it or not , I also got the same idea, when I searched in the Help Doc today .

Got some problem in my n/w connection, so couldn't replied to this post earlier.

Thanks,
Rifaz