Jim Horn
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_po licies_by_ plst;1.mon th_id});
numberVar y := int(x/100);
numberVar m := x - (y * 100);
date(y, m, 1);
I like bdreed's approach though - much more elegant.
numberVar x := cint({spr_due_diligence_po
numberVar y := int(x/100);
numberVar m := x - (y * 100);
date(y, m, 1);
I like bdreed's approach though - much more elegant.
ASKER
(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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>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
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
ASKER
numberVar x := {spr_due_diligence_policie
numberVar y := int(x/100);
numberVar m := x - (y * 100);
date(y, m, 1);