Convert Numeric field into Date field

[RptSm]

I am trying to convert a date field that is stored in the AS400 as a numeric field. The data is YYYYMMDDMMSS. However, when displayed in Crystal 10 or 11 is shows commas in between for example 20040120.00. The formula I am trying to use is as follows:

NumberVar input:={CKSCHEDE.SEFRDT};

Date (Val(ToText(input,0,'''')[1 to 4]),
Val(ToText(input,0,'''')[5 to 6]),
Val(ToText(input,0,'''')[7 to 8]))

The error I keep receiving is that the month must be between 1 & 12. I would appreciate any help.

Thanks

 

[synapsevampire]

Try:

Local stringvar MyDate := totext({CKSCHEDE.SEFRDT},0,"");
local datevar OutDate;
if len(trim(MyDate)) = 8 then
OutDate := cdate(left(MyDate,4),mid(MyDate,5,2),mid(MyDate,7,2))
else
OutDate := cdate(1970,1,1);
OutDate

-k

 

[lbass]

Please show samples of your field again--you say it is formatted: YYYYMMDDMMSS, but your sample only shows 10 digits.

It sounds like you might have some bad year data, since I think your formula should otherwise work. Maybe try sorting your field to see if you have some years expressed in only two digits or something.

-LB

 

[synapsevampire]

I should have used:

Local stringvar MyDate := totext({CKSCHEDE.SEFRDT},0,"");
local datevar OutDate;
if len(trim(MyDate)) = 8 then
OutDate := cdate(val(left(MyDate,4)),val(mid(MyDate,5,2)),val(mid(MyDate,7,2)))
else
OutDate := cdate(1970,1,1);
OutDate


But Ido makes a good point, show actual examples because dates stored as strings tend to include hours:minutes:seconds, you state that there isn't an hours place.

-k