How to convert JD Edwards EnterpriseOne (JDE) Julian Dates

In Custom Development, Enterprise Systems, JD Edwards by Bryant Avey0 Comments

Overview

If you’ve ever worked with Oracle/PeopleSoft’s EnterpriseOne ERP system (also known as JD Edwards or simply JDE) you’ll quickly find that dealing with dates from the database is a bit challenging.  EnterpriseOne stores all dates in the system in Julian Date format.  EnterpriseOne has a lot of dates in every table.

The Julian Date in EnterpriseOne goes back to the days before EnterpriseOne was called EnterpriseOne; back when it was just JD Edwards and it ran on an AS/400 (now called an iSeries or Series i).  Back then, disk space (DASD) was a premium.  By storing all the dates in 6-digit Julian format, the numbers could be packed (compressed) to save table or database space.  Additionally, since Julian Dates are in a fairly simple numerical form, it was easy to develop routines and functions to add, subtract, and otherwise calculate dates.

Over the years, disk space has turned into a commodity.  Modern computer languages and SQL  can easily deal with date calculation.  But EnterpriseOne data is still in this Julian Date format.   So how do we deal with it, and how do we get it converted into a normal datetime data type?

This article will cover how to create and use two SQL functions to convert Julian dates to Gregorian dates and then to convert Gregorian dates to EnterpriseOne Julian format.  Many of my clients use these two functions for SQL Server Reporting Services (SSRS) Reports, and various EDI interfaces, and XML feeds.  These two functions are valid on any version of JD Edwards including World, OneWorld, XE, or the latest version of EnterpriseOne.  If you want to use the functions on World, or directly on an IBM Series i, you’ll need to do a bit of syntax tweaking.  If you’re accessing the Series i (AS/400) from SQL Server, then this function will work perfectly for you.

EnterpriseOne Julian format

All versions of JDE use a Julian Date format as follows: CYYDDD where C = Century; YY = a 2 digit year; and DD = the 3 digit number representing the day of the year (1 through 365 or 366 days on a leap year).  The Century is either a 1 or 0 depending on whether you’re using year 2000 + or if you’re using dates in the 1900’s.

For example, the Gregorian Date 01/01/2009 would be Julian Date 109001 where 1 = century 2000, 09 = the last 2 digits of the year 2009; and 001 = the first day in 2009. Similarly 12/31/2009 would be represented as 109365 where the only change is the day number being 365 to represent the last day in 2009.

Note: A value of zero(0) in JDE Julian date format represents 12/31/1899 and 01/01/1900 is the numerical value 1 in Julian format.

Converting Julian Dates to Gregorian – Date J2G( )

The first function we’re going to write is one called DateJ2G() meaning “Date Julian to Gregorian”. This function has two parameters, one parm for the Julian date you wish to convert, and one parm for the format style code, which represents the format you want your Gregorian date.  The DateJ2G() function will convert the date to any standard SQL Server formatted date style.  A complete list of format styles can be found at at https://msdn.microsoft.com/en-us/library/ms187928.aspx.

Here’s the code to create the function:

To use the DateJ2G function, just pass in a date and style format as described in the comments contained in the above code.

Converting Gregorian Dates to Julian – DateG2J( )

The next function will convert any standard formatted Gregorian date string to a JDE EnterpriseOne Julian date.

Here’s the code to create the DateG2J function:

Stewart Johnston – Submitted this on 2013/07/10.
If you’re wanting to do it in Oracle, this
convert an individual field in a query:

To use the DateG2J function just follow the examples outlined in the code above.

Conclusion

As you can see, there are only a few lines of SQL code needed to convert between Julian and Gregorian dates in JDE EnterpriseOne.  I used February 29, 2008 (leap year) dates in the examples to show that the functions correctly handle leap years.

Let me know how this has worked for you.