Need Help converting to Date

[Maker1]

I am using Crystal 8.5 and trying to pull information off of an AS/400 system. The problem I am having is converting the date format used in the tables into a readable date. Our system uses a Julian Date type system only the year 2000 is listed as 100, 2001 shows up as 101, etc. with the day of the year listed as a number. So for example Jan 1, 2001 will show up as 101001 and December 31, 2002 would be 102365. I can break it apart to fix the year but don't know if Crystal will convert the number into a month and day. Any idea's?

 

[Madawc]

You need the date command, format

Date (YYYY, MM, DD)

Use three formual fields to turn your Julian date into DD, MM and YYYY functions, with some sort of convert for year. For the dates you showed,

"20" & Right(Julian, 2)

would work.

Do a fourth formula field to turn the parts into a date.

It is also worth doing a Search here for Julian date, someone may already have a solution, maybe a better solution. And look at the FAQs.

Madawc Williams (East Anglia)

 

[bdreed35]

This will work for you as long as all dates are from the year 2000 on:

//@ConvertDate
WhilePrintingRecords;
datevar result := date(1999,12,31);
numberVar tempY := tonumber(mid(totext({table.jdate},0,"",""),2,2));
numberVar tempD := tonumber(right(totext({table.jdate},0,"",""),3));

result := DateAdd("d",tempD,DateAdd("yyyy",tempY,result));

result;

~Brian

 

[synapsevampire]

You can also just use the Julian Date UFL add on from Crystal:

http://support.businessobjects.com/communityCS/FilesAndUpdates/ufljul.exe.asp

-k

 

[Maker1]

Found the information on the Support website:

StringVar JDEDate;
NumberVar JDECentury;
NumberVar JDEYear;

JDEDate := ToText({table.field}, "000000");
JDECentury := ToNumber(JDEDate[1]);
JDEYear := ToNumber(JDEDate[2 to 3]) + 1900 + (100 * JDECentury);

(Date (JDEYear,01,01) + (ToNumber(JDEDate[4 to 6]) - 1));

Was the only thing that worked.

THANKS