Using JD Edwards Date in IDEA

Brian Element

Forums

A friend of mine recently had a problem converting the date field from a JD Edwards sytem.  The JD Edwards date is a bit unique in that it is 6 digits long with the first digit indicating the century, the next two indicating the year and the final three indicating the number of days within the year.  I found this page that talks about the JD Edwards Date Conversion.  An example they use on the page would be 105031 would be January 31, 2005, the one would indicate it is the century is 20, the 05 is the year and the 31 is the number of days since January 1.  I used the following equation to convert the number into a date field:

@Ctod(@Str((1900 + (DATE/1000)), 4, 0) + @Right(@Str(DATE, 6, 0), 3), "YYYYDDD")

So first thing you do is you divide by 1000 to get the year and add 1900, you then take the last three digits and put both into a character field, then the @ctod function can now read them.

Steven Luciani Mon, 12/15/2014 - 12:23

I just happen to be working on a JDE file right now. Another way to tackle the date issue is with the equation below:

@if(@mid( gldicj_car  , 1 , 1) =  "1"  , @ctod(@mid(gldicj_car , 2 , 5) ,  "yyddd" ) , @ctod(@mid(gldicj_car, 1 , 5) ,  "yyddd"  )  )

This accounts for a situation where the leading "1" may not be present in a particular JDE date field.

If you are importing these types of files frequently consider adding virtual fields to your record definition that convert all the date fields during the import process using one of the two equations in this post. You will save yourself lots of time.

Cheers.