AS400/MAPICS Date Conversion

[pjl923]

I'm using CR 8.5 to pull data off AS/400 running MAPICS XA r6. DATE fields come over as a numeric (e.g. 1,050,307.00; which is 03/07/05). I've pulled down from the BO website a function 'NumberToDate' which converts the field to a number. The problem is the century code of '1' at the beginning. Above example translates as '3/7/105'...which CR can't recognize as a true date and you can't use any of the DATE functions to select, calculate, etc. You can change the format to display as '3/7/105', but that's only aesthetics.
Any ideas?

 

[synapsevampire]

Create a date from it:

whileprintingrecords;
stringvar MyDate:=totext({table.date},0,"")
numbervar Myyear;
numbervar Mymonth:= val(mid(MyDate,4,2));
numbervar Myday:= val(mid(MyDate,6,2));

if left(MyDate,1) = 1 then
Myyear:= 2000+val(mid(MyDate,2,2))
else
Myyear:= 1900+val(mid(MyDate,2,2));
cdate(Myyear,Mymonth,Myday)

Now use this formula in lieu of the table.date field.

-k

 

[pjl923]

Thanks so much...works great.

How can you use this as a parameter/select field though?