crystal reports formula for julian date conversion from JDE

[Former Member]

Hello world and Happy Friday!... I am attempting to pull dates from JD Edwards using Crystal Reports but I am not having any luck.. here is my code

ToText({table.INdate},(1900),1,1)

it looks like this:

1/27/-4436

please advise thanks in advance

[JWiseman]

hello L Hale and happy friday to you as well.

there's a lot of references to a ufl that does conversions to julian dates but i'm not a huge fan of ufl's as you have to install them everywhere and they're not portable with the report.

so, i've come up with a custom function that does the conversion instead...you can add this to your businessobjects or crystal reports server repository should you be using a server platform. that way you can quickly use it on new reports. otherwise you can just follow the steps below on a new report should you wish to have the function in different reports.

steps:

1) go to your Formula Expert and choose to create a new Custom Function

2) name the function convJulian

3) paste the following syntax into the function

Function (numbervar dbjd)

stringvar jd:= totext(dbjd,0,"");

if length(jd) = 5 then
(
numbervar yr:= 1900 + tonumber(jd[1 to 2]);
numbervar da:= tonumber(jd[3 to 5]) -1;
datevar od:=  date(dateadd("d", da, date(yr, 01, 01)));
)

else

if length(jd) = 6 and jd[1] = '0' then
(
jd:= jd[2 to 6];
numbervar yr:= 1900 + tonumber(jd[1 to 2]);
numbervar da:= tonumber(jd[3 to 5]) -1;
datevar od:=  date(dateadd("d", da, date(yr, 01, 01)));
)

else

if length(jd) = 6 and jd[1] <> '0' then
(
jd:= jd[2 to 6];
numbervar yr:= 2000 + tonumber(jd[1 to 2]);
numbervar da:= tonumber(jd[3 to 5]) -1;
datevar od:=  date(dateadd("d", da, date(yr, 01, 01)));
)

;

od;

4) now create a new formula which will output the conversion

convJulian ({your julian date field})

cheers,

jamie

[Former Member]

thx!