Numeric Field Conversion to a Valid Date Format

[AJ2010]

I have a source database file field which holds the date in CYYMMDD format, 'C' holds a value of either '0' or '1', '0' denotes a Century starting 19 and '1' denotes 20.

Field value of 1100607 = 20100607 and
Field value of 0991231 = 19991231

I've created the following Formula Fields which seem to do the trick however when I automate the Report I experience an error which states that the Month Value must be a value in the range of 1 to 12. I have checked the Source Data File and all values look to meet my expectations in relationship to CYYMMDD format.

FORMULA FIELDS:
================
"DateAsText" (Order Creation Date):
if {ORDERHDR.DTCO40}<=1000000 then "0"+ CStr ({ORDERHDR.DTCO40}) else CStr ({ORDERHDR.DTCO40})

"Year" (Order Date YYYY):
if left({@DateAsText},1) = "0" then "19" + mid({@DateAsText},2,2) else "20" + mid({@DateAsText},2,2)

"RealDate" (Order Date in YYYYMMDD):
CDate(val({@Year}),val(mid({@DateAsText},4,2)),val(mid({@DateAsText},6,2)))

Any ideas into a better or more robust way?

 

[lbass]

This looks okay to me, so I wonder whether you are hitting a null. Try replacing {@DateAsText} with:

if isnull({ORDERHDR.DTCO40}) or
{ORDERHDR.DTCO40} = 0 then
"99990909" else
totext({ORDERHDR.DTCO40},"00000000")

What do you mean by "when I automate"? Are you saying your formula works in CR, but fails when accessed through another application?

-LB