Midrange Guru

OS/400 Edition
Volume 3, Number 7 -- February 5, 2003

Real Date to JD Edwards Date as a UDF

Dear Readers:

In the November 6, 2002, issue of Midrange Guru, I showed how to convert a DB2 native date to a JD Edwards date and promised you a user-defined function that would do the conversion. This article delivers on that promise.

Converting

JD Edwards stores dates in a decimal(6,0) field in the format CYYDDD where C is 0 for years before 2000 and 1 for 2000 through 2099, YY represents the two digit year, and DDD is the day number from January 1. So, representing 01/01/2002 is 102001.

I have written two articles, "Convert JD Edwards Dates to Other Formats" and "JD Edwards Dates, Revisited," about converting from and to JDE dates, so refer to those articles if you want an explanation of how the conversion works. Rest assured that the following formula will convert a date to JDE format:

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

Before we begin the function, let's create a table and load some sample data into it:

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');

Now we can test the formula by invoking the following statement:

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

This statement will yield this table:

D	RD	JDEDATE
99001	01/01/1999	99001
100001	01/01/2000	100001
101001	01/01/2001	101001
99034	02/03/1999	99034
100066	03/06/2000	100066
101098	04/08/2001	101098
101365	12/31/2001	101365

Now that you see the formula work in a statement, let's create a user-defined function that does the conversion for us. Here is the code for the function:

CREATE FUNCTION WEBOE.REALTOJD
( RD DATE)
 RETURNS  DECIMAL(6,0)
 LANGUAGE SQL
RETURNS NULL ON NULL INPUT
 DETERMINISTIC 
NO EXTERNAL ACTION 
 

  /*Begin Function Code*/

BEGIN 

 DECLARE F_OUTPUT DECIMAL(6,0) ;
 SET F_OUTPUT = DECIMAL((YEAR(RD)-1900)*1000+DAYOFYEAR(RD),6);
 RETURN F_OUTPUT ;
END

You can create this function on your AS/400 by pasting this code into a Run SQL Scripts session under Operations Navigator, executing it in my SQLThing Enterprise Edition tool, or using the Start SQL Interactive Session (STRSQL) command. Once the code listed above is executed, you will have a UDF called REALTOJD in the WEBOE library on your AS/400.

Note that the function takes a DATE value and returns it as a DECIMAL(6,0) data type. The input variable is called RD, (stands for Real Date). All the function does is declare an output variable, F_OUTPUT as type DECIMAL(6,0), assigns the result of our handy real to JDE formula and returns the variable to the caller. Quite simple really, but easier to type than the formula when you want to do a conversion.

The following statement will use the function:

SELECT D, RD, REALTOJD(RD) FROM MCEIS.T1

If for some reason the function does not work, you probably do not have the library that contains the function in your library list. To rectify this, execute the following statement:

SET CURRENT FUNCTION PATH = WEBOE;

WEBOE is the name of the library that you created the function within.

But I'm not on V5...But I don't have a C compiler...

Ok, don't whine, you can still have the function thanks to the AS/400's wonderful backwards compatibility. I have a friend with an AS/400, but he is on V4R4 and also does not have the C compiler. So, I made one simple change to the above function, (I added a set option for compatibility back to V4R4), and then compiled it on my system. I made a save file and saved the service program into it. I then sent it to my friend, and he restored the service program to his machine. Bang, he can now use the function on his AS/400. When you restore a user-defined function from a save file, the AS/400 automatically makes all of the updates to the system catalog and registers the function to DB2. This is very useful, as you can develop objects like stored procedures and functions on a development machine and then restore them to a production machine that maybe doesn't have the C compiler or the SQL development kit. Or in this case, I can put the function on my Web site and you can download it and put it on your machine.

I have placed a save file on my Web site containing this user-defined function with detailed instructions on how to restore it to your AS/400. In a nutshell, simply go to my Web site, http://www.sqlthing.com/resources.html , read the instructions, and download the save file to your PC. Then, transfer the save file to your AS/400 and use restore object to restore the function to your AS/400. Now you can use the REALTOJD function too.

-- Howard

Howard F. Arner, Jr. is a writer and consultant for Client Server Development, Inc. in Jacksonville, Florida. He is also the author of "iSeries and AS/400 SQL at Work," which you can purchase from his Web site: http://www.sqlthing.com/books, or visit Howard's Web site to find out more about user-defined functions on the AS/400.