JDE Jul date to ISO Format

Hi Experts,

Im digging in some java code and I found the following part in the Select part of a SQL definition see attached code,  just to get a jul-date from JDE into a ISO date format (YYYYMMDD).
and this is used for each of the date fields in the SQL def.

Is there no other solution then this garbage?
something maintainable?
a standard function?
a own written function?
an Include file? whatever

my java knowledege is verry low so please help me with an example.

Thanks


case when (a.JDAT IS NULL " +                                
or a.JDAT < 1011) then '0' ELSE " +                          
(to_char(to_date(to_char((1900 +  " +                          
(100 * TO_Number(substr(to_char(a.JDAT),1,1)))) " +          
+ substr(to_char(a.JDAT),2,2)) || '-01-01', 'yyyy-mm-dd') " +
+ (substr(a.JDAT,4,3) - 1), 'yyyymmdd')) END JDAT, " +

Open in new window

LVL 17
MurpheyApplication ConsultantAsked:
Who is Participating?
 
MurpheyApplication ConsultantAuthor Commented:
Found the answer :-S

TO_CHAR(TO_DATE(TO_CHAR(a.JDAT+1900000), 'YYYYDDD'), 'YYMMDD')
0
 
CEHJCommented:
You'll have to forgive me - i don't know what 'JDE' is, nor 'Jul'. What datatype are you starting with?
0
 
MurpheyApplication ConsultantAuthor Commented:
Hi CEHJ

JDE = JD Edwards a (Software package) MRP, sales etc.

Jul = Julian date format = year/daynumber
normally it's yyddd, but JDE thinks to be clever and uses cyyddd, so standard Jul2date routines don't work
here

regards,
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CEHJCommented:
OK

>>What datatype are you starting with?
0
 
MurpheyApplication ConsultantAuthor Commented:
Data type is uhhh Character
0
 
CEHJCommented:
Try the following. You can get 'fromJulian' here:

http://www.rgagnon.com/javadetails/java-0506.html
	int jul = 2436660; (Julian date)
	int[] atoms = fromJulian(jul);
	Calendar cal = new GregorianCalendar(atoms[0], --atoms[1], atoms[2]);
	String iso = new java.sql.Date(cal.getTimeInMillis()).toString();
	System.out.println(iso);

Open in new window

0
 
MurpheyApplication ConsultantAuthor Commented:
Hi CEHJ,

I can't include your solution in a SQL string... and... its using the standard Julian date, not the stupid JDE one.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.