Going from a True Date to a JDE Date

November 6, 2002 Timothy Prickett Morgan

Hey, Howard:

The function you wrote in the Midrange Guru article, “JD Edwards Dates, Revisited ,” does a great job of converting a JDE date to a ‘true’ date. But what about converting a true date to a JDE date? Suppose I have a true date, for example the ‘current date’ and I want to convert that to a JDE date?

— Andy

Wow, I did not even think of the reverse, and now that you brought it up I see what a gaping hole that is. I can image a lot of situations where you need to go from ‘real’ dates to JDE dates, so let’s get cracking on a formula to do that!

For review, JDE dates are stored in a decimal(6,0) field in the format CYYDDD, where C is 0 for years before 2000 and 1 for years of 2000 or after, YY represents the two-digit year, and DDD is the day number from January 1. So, representing 01/01/2002 in JDE is 102001.

Now, I have written two previous articles on how to convert JDE to real dates. The first article, “Convert J.D. Edwards Dates to Other Formats,” provides the formula to use in an SQL statement, whereas the second article, which you mentioned in your question , shows how to do it with a user-defined function. Review those articles to learn how to convert from JDE to a date.

Before we attempt the formula, let’s create a table and add some sample data:

CREATE TABLE MCEIS.T1
   (D DECIMAL(6,0), RD DATE);

INSERT INTO MCEIS.T1 VALUES (99001,'01/01/1999');
INSERT INTO MCEIS.T1 VALUES (100001,'01/01/2000');
INSERT INTO MCEIS.T1 VALUES (101001,'01/01/2001');
INSERT INTO MCEIS.T1 VALUES (99034,'02/03/1999');
INSERT INTO MCEIS.T1 VALUES (100066,'03/06/2000');
INSERT INTO MCEIS.T1 VALUES (101098,'04/08/2001');
INSERT INTO MCEIS.T1 VALUES (101365,'12/31/2001');

The above statements create a table called T1 in the library MCEIS that contains two fields: D that is meant to hold JDE dates and RD that holds the corresponding real date. The next statements add records to the tables that show the JDE date and the corresponding real date. Once you have the sample data, it’s time to move to the function.

An Ugly Way

I’m going to show you two ways to convert the dates. The second one is more elegant, but I want you to see the first method because it illustrates some interesting side effects of SQL math.

First, let’s worry about how to extract the century flag. The YEAR SQL function can be used to return the year portion of a date as an integer. So the following portion of the formula will yield 0 for years prior to 2000 and 1 for years 2000 and after.

YEAR(RD)/1000-1

Ah, I bet you are thinking, “Howard, 1999/1000 is 1.999. Your formula won’t yield 0 for years less than two thousand!” Remember, the YEAR function returns an INTEGER data type, and since you are dividing by a whole number the AS/400 will use integer division, which truncates the result. This is one place where you need to understand how the AS/400 does math within SQL. In this case, the AS/400 is saving you a lot of time by not requiring you to cast the result of the division back to integer. Since both operands are integer, the result is integer.

Next, we need to extract the last two digits of the year value.

MOD(YEAR(RD),100)

The above expression uses the MOD function to return mod 100 of the year portion of the date. If the date is 1999, this would return 99. If the date is 2001, this would return a number 1.

Finally, we need to extract the days portion of the date in order to create our JDE date. The DAYOFYEAR function returns the day number of the year that the date represents. If the input date is 01/01/2003 then the result would be 1. If the input date is 02/03/1999 then the result would be 34.

Now that you understand the elements of how I am going to manipulate the date, let’s take a look at the function in its entirety and see how I use other SQL functions to transform the individual parts into a string representation of the date.

DIGITS(DECIMAL(YEAR(rd)/1000-1,1))||
DIGITS(DECIMAL(MOD(YEAR(rd),100),2))||
DIGITS(DECIMAL(DAYOFYEAR(rd),3)),6,0)

The above expression uses the concatenation operator, ||, to concatenate the results of the three expressions into a character string representing the JDE date. The DECIMAL function is used around each conversion to ensure that the number is cast to an appropriate DECIMAL length so that the DIGITS function will operate correctly. DIGITS looks at the length of a decimal number and returns a zero-filled character string of that length. So, if your number is 1 and you cast to DECIMAL(3) and then ask for DIGITS, you would return the string ‘001,’ which is the zero-filled CHAR(3) representation of the decimal(3) number 1.

If we do the above expression in a select statement against the test table, we will see the following results:

SELECT d, rd,
      DIGITS(DECIMAL(YEAR(rd)/1000-1,1))||
      DIGITS(DECIMAL(MOD(YEAR(rd),100),2))||
      DIGITS(DECIMAL(DAYOFYEAR(rd),3))
      AS JDEDATE
FROM mceis.t1;

That result would yield this information:

We are almost there. We just need to convert the string into a DECIMAL(6,0) field so that we can use it in JDE. The following statement will return the RD column as a valid JD Edwards DECIMAL(6,0) date:

SELECT d,rd,
DECIMAL(DIGITS(DECIMAL(YEAR(rd)/1000-1,1))||
DIGITS(DECIMAL(MOD(YEAR(rd),100),2))||
DIGITS(DECIMAL(DAYOFYEAR(rd),3)),6,0)
AS JDEDATE
FROM mceis.t1;

That will yield the following information:

A Better Formula!

It has been said that within every large program, there is a small program struggling to get out. Maybe the same is true of SQL queries. Here is an easier formula than the first one I developed.

(YEAR(RD)-1900)*1000+DAYOFYEAR(RD)

Here is the SQL statement that will use the second formula and return the data as a DECIMAL(6,0):

SELECT D, RD, 
DECIMAL((YEAR(RD)-1900)*1000+DAYOFYEAR(RD),6) AS JDEDATE
FROM MCEIS.T1;

That yields the following information:

In an upcoming issue of Midrange Guru, I will write a user-defined function to perform this conversion. I will provide both the function code and a downloadable version of the function available from my Web site so that users with less than V5R1 or no access to the C compiler can use the function.

— Howard

Howard F. Arner, Jr. is a writer and consultant for Client Server Development, Inc. You can purchase Howard’s book, “iSeries and AS/400 SQL at Work” from www.sqlthing.com/books or go to www.sqlthing.com to find out more about manipulating dates on the AS/400.

Sponsored By ADVANCED SYSTEMS CONCEPTS

Business Analytics – Practical – – Cost Effective – – Easy to Deploy – SEQUEL FYI User Quote: “I love SEQUEL FYI because it lets me look at the data any way I need to see it, instantly. This is the easiest tool to manage complex product relationships that I have ever seen.” SEQUEL FYI offers outstanding OLAP business intelligence functionality for a fraction of the cost of comparable solutions. Read More > View Streaming Video