Convert JDE date format to Gregorian date
J.D. Edwards (JDE) stores dates as a 6 digit string, referred to as Julian date format by JDE/Oracle*. See the syntax below for converting this string value to a standard date format in Paxata.
The first digit represents the century ( 0 = 1900’s, 1 = 2000’s, 2 = 2100’s, etc)
The second and third digits represent the year of the century
The 4th, 5th and 6th digit represent the day of the year.
Convert JDE dates (Julian) to “regular” dates (Gregorian) in Paxata using this formula:
DATEVALUE( CONCATENATE( STR(1900 + INT(LEFT(@Gregorian_2_JDE@ ,3))) , RIGHT(@Gregorian_2_JDE@ ,3) ), "yyyyD")
If, for some reason, you wanted to go the other direction (convert Gregorian dates to JDE format), this formula can be used:
IF(YEAR(@date@ ) - 1900 > 0, CONCATENATE( RIGHT(CONCATENATE("000",STR(YEAR(@date@ ) - 1900 )),3), RIGHT(CONCATENATE("000", DATEFORMAT(@date@ ,"D")),3) ), "no dates prior to 1/1/1900 allowed" )
You can find more information about JDE Julian date formats here:
You can find more information about true Julian dates here: