Link to home
Start Free TrialLog in
Avatar of Jim Horn
Jim HornFlag for United States of America

asked on

Formula help: convert YYYYMM string to MMM YY (200605 to May 2005)

Hello fellow experts

I have a crosstab report where the SQL SP passes months in YYYYMM char(6) format, and I am using them as crosstab column headers.

How in the formula editor can I convert this YYYYMM value to MMM YY (i.e. 200605 to May 2005)

TIA
-Jim
SOLUTION
Avatar of frodoman
frodoman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jim Horn

ASKER

I am getting a 'A number is required here' error message, and apparently need to covert my char(6) value to numeric.

numberVar x := {spr_due_diligence_policies_by_plst;1.month_id};
numberVar y := int(x/100);
numberVar m := x - (y * 100);
date(y, m, 1);
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This will fix it if it's not numeric - you could also have a null value that is causing the error.

numberVar x := cint({spr_due_diligence_policies_by_plst;1.month_id});
numberVar y := int(x/100);
numberVar m := x - (y * 100);
date(y, m, 1);

I like bdreed's approach though - much more elegant.
(N00b comment)  Pleasant, now I am unable to get back into the Formula Editor for this field, which is a row header field in a crosstab.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>Try it from within the crosstab expert.
Had to go Format menu, Crosstab..., as Crosstabl was not a right-click option.

>Click on the formula in the Row box and then you should be able to click the "Edit Formula" button
That did it.  

Points to both for outstanding assistance.
-Jim