How to convert Julian dates from JDEdwards tables 2

[ewarr]

How do I convert Julian dates from JDEdwards tables to normal format (xx/xx/xxxx) ? Currently I'm using a cross-ref table in Excel but I'm thinking Crystal might have a formula?

Thanks

 

[Turkbear]

Hi,
Try here:

thread767-1495304





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ewarr]

My Julian date is a different format coming from JDEdwards:

110196 = 07/15/2010

with char 2,3 being the year 2010 and 196 being the day of the year. Not sure the purpose of leading "1" but I don't need the first character....just the last 5.

Any suggested formulas?

Thanks

 

[briangriffin]

Offhand I don't see any flaws in this, but test it with leap year, etc. If all checks out I'd compile it as a custom function.

whileprintingrecords;
numbervar x;
numbervar y;

x := 2000 + tonumber(mid(totext({juliandate},"#",0),2,2));
y := tonumber(right(totext({juliandate},"#",0),3)) - 1;

dateadd("d",y,date(x,1,1))

 

[Turkbear]

Hi,
Try using the MID function to parse out the needed bits and then build up the Date using the DateAdd and Date functions.
Something like:
@NormalDate

StringVar Year := '20' + Mid({ JDEdwardsDatefield},2,2)
StringVar  DayofYear := Mid(JDEdwardsDatefield},4,3)
Date(DateAdd("d",DayOfYear,Date(Year,01,01))

Some text to number conversion may be needed, I can't test it at present.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ewarr]

Thanks to all! I used this code from briangriffin and it worked great:

whileprintingrecords;
numbervar x;
numbervar y;

x := 2000 + tonumber(mid(totext({juliandate},"#",0),2,2));
y := tonumber(right(totext({juliandate},"#",0),3)) - 1;

dateadd("d",y,date(x,1,1))

Nice job....thanks again!