<Back

Working with the JDEdwards Date Format

The way that JDEdwards OneWorld and World (Classic) stores dates makes dealing with data directly more difficult than it needs to be.  Below is some code that I have used to deal with JDEdwards date formats

It should be pointed out that we are not suggesting that you change the data in the JDEdwards database through any means other than the JDEdwards toolsets, unless you have absolute understanding of the implications.  These routines are useful for reading the data into another tool through ODBC or similar, or the creation of interfaces between JDEdwards and othe rsystenms you are running.

For those of you that don't know the date format stored is as follows

CYYDDD

C=Century, 0=19, 1=20, 2=21
YY=Last two digits of the year
DDD=day where 001=01 Jan, and 365=31 Dec or 30 Dec (leap year)

Examples

Microsoft Access

Create a new module, put these functions in it.  When designing a query you can then use then by giving them the jde field to process.  

Public Function jdeToDate(jdedate As Long) As String
Dim year, days As Integer
If jdedate <> 0 Then
    year = (jdedate \ 1000) + 1900
    days = jdedate Mod 1000

    jdeToDate = DateAdd("d", days - 1, "01/01/" & year)
Else
    jdeToDate = 0
End If
End Function

Public Function dateToJde(theDate As Date) As Long
Dim year, day As Integer

year = DatePart("yyyy", theDate)
day = DatePart("y", theDate)

dateToJde = (year - 1900) * 1000 + day
End Function

Oracle PL/SQL

The following code creates a function jde2date, which you can then call passing in the JDEDate and will get a string returned in the current oracle date format.  Also the complementary function date2jde which will make an oracle date into a jdedate.

create or replace
function jde2date( jd_date in number ) return char
is
    thedate char(9) default '         ';
begin
    if jd_date <> 0  then
       thedate := to_char(to_date((to_char(jd_date+1900000)),'yyyyddd'));
    end if;
    return thedate;
end;
/

create or replace
function date2jde(theDate in date) return integer
is
  jdedate integer default 0;
begin
        jdedate := to_number(to_char(theDate,'yyyyddd'))-1900000;
        return jdedate;
end;
/

If you want to convert a date that is not in the current default format for oracle you will need to run it through the to_date function before passing it to the date2jde function. Example of to_date function TO_DATE ('November 13, 1992', 'MONTH DD, YYYY')

To find the day of todays date using SQLPlus

select to_char(sysdate,'ddd') from dual;

C

The following C code will create an application that called on the command line with one parameter which is the JDE date, will return a real date.  This was written to run on a UNIX box for data export from JDE, but could be used for any number of purposes.

/* Short program to convert a JDE Date format */
/* into a text format suitable for interfaces */
/* to other systems.                          */
/* Written by Ian Connelly                    */
/* Myriad IT Services Ltd                     */
/* April 2002                                 */

#include <stdio.h>

main(int argc,int *argv)
{
        int mthd[]={31,28,31,30,31,30,31,31,30,31,30,31,999};
        char
*mth[]={"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"};
        char *err[]={"",
                "Too many days for year",
                "Zero days invalid",
                "No year information"
        };
        int cnty, year, ddd, jded, m, yyyy;
        int invalid=0;

        if (argc<2){
                printf("usage: %s <jdedate>\n",argv[0]);
                exit(1);
        }

        jded=atoi(argv[1]);
        ddd=(jded%1000);
        year=(jded%100000)/1000;
        cnty=jded/100000+19;
        yyyy=year+cnty*100;


        /* Handle Lear Year */
        if ((yyyy%400 == 0)||((yyyy%100 != 0)&&(yyyy%4 == 0))){
                mthd[1]=29;
                if (ddd>366){
                        invalid=1;
                }
        }else{
                if (ddd>365){
                        invalid=1;
                }
        }

        if (ddd<1)
                invalid=2;
        if ((year==0) && (cnty==19))
                invalid=3;

        m=0;
        while(ddd>mthd[m]){
                ddd=ddd-mthd[m];
                m++;
        }

        if (invalid){
                /* Print error message */
                printf("%s:%s\n",err[invalid],argv[1]);
                exit(1);
        }else
                printf("%.2d-%s-%.2d\n",ddd,mth[m],year);

        exit(0);
}