AS400 string to date

[ericfmyers]

I'm pulling dates from an AS400 database but when I try to use the CDate formula it's not returning the correct results.
For example, the field contains the value:

1080914

This formula:

CDate({TRH00.TXDATE})

returns the date 6/9/4859 which is wrong.

The correct date should be 9/14/2008.

Thank you for any help you can give.

 

[lbass]

Try:

stringvar x := {table.stringdate};
date(1900+val(left(x,3)),val(mid(x,4,2)),val(right(x,2)));

-LB

 

[ericfmyers]

I tried that and then it was asking for a string then I realized that this is a number field. So in the formula I converted the field to a string then I added your code under it but now I'm getting the error:
"A month number must be between 1 and 12"

So then I tried:

numbervar x := {TRH00.TXDATE};
date(1900+val(left(x,3)),val(mid(x,4,2)),val(right(x,2)));

It now wants a string in place of the second line's X's.

 

[lbass]

Use:

stringvar x := totext({table.numberdate},0,"");
date(1900+val(left(x,3)),val(mid(x,4,2)),val(right(x,2)));

-LB

 

[ericfmyers]

That worked perfectly, thanks!