Convert JDE date format to Gregorian date

August 6, 2018

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:
https://docs.oracle.com/cd/E26228_01/doc.93/e21961/julian_date_conv.htm#WEAWX259

You can find more information about true Julian dates here:

http://aa.usno.navy.mil/data/docs/JulianDate.php

Get Started
Show Buttons
Hide Buttons