JD Edwards Dates, Revisited

September 20, 2002 Timothy Prickett Morgan

Dear Readers:

After I published the piece on how to convert JD Edwards date to DB2 date data types, I received more mail than I have ever received before.

Obviously, you guys are salivating for date routines. Well, so as not to let you down, I will publish in this issue a user-defined function that you can use in place of the date conversion formula. However, for those of you that do not have V5 or the C Compiler and SQL Development Kit, I will provide instructions on how you can download a working version of the function and put it on your machine. Even if you cannot create the function yourself, you can at least put it on your machine and use it.

The Function

If you remember my article from the September 6 issue of Midrange Guru, OS/400 Edition, here’s one formula for converting JD dates to real dates:

DATE(digits(DECIMAL(JDEDATE+1900000,7,0)))

This formula uses simple addition and a few SQL scalar functions to make the JD date into a real date. Once the data is a date, you are home free, as you can use all of the built-in DB2 date functions to do date math and manipulation.

The following SQL source code is a user-defined function that will convert JD dates:

CREATE FUNCTION MCEIS.JDDCONV
   (JDEDATE DECIMAL(6,0))
   RETURNS DATE 
   LANGUAGE SQL
   SET OPTION DATFMT=*ISO
BEGIN 
DECLARE F_OUTPUT DATE ; 
DECLARE F_TEST INTEGER ; 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET F_TEST = 1 ; 
SET F_TEST = 0; 
SET F_OUTPUT = DATE(DIGITS(DECIMAL(JDEDATE+1900000,7,0)));
IF F_TEST = 0 THEN RETURN F_OUTPUT ; 
ELSE RETURN NULL; 
END IF; 
END

You can create this function on your AS/400 by pasting this code into a run SQL Scripts session under Operations Navigator (OpsNav) and running it, executing it in my SQLThing tool or using STRSQL. Once the code (displayed above) is executed, you will have a UDF called JDDCONV in the MCEIS library on your AS/400.

Note that the function takes a DECIMAL(6,0) value and returns it as a DB2 date data type. Also, the function uses the SET option to ensure that, when the function is executing, it deals with all date data in *ISO format. This ensures that even if your job is running in a different format, within the function scope the value will be treated as an *ISO date value.

When the function executes, it first declares two variables: F_OUTPUT as a DATE and F_TEST as an integer. F_OUTPUT will be used to hold the results of the date calculation, and F_TEST will be used to test whether the conversion was successful. The function next declares a continue handler for any SQLEXCEPTION. A handler is a basic form of error handling available within user-defined functions and stored procedures. A continue handler is like a trap that instructs the program to continue processing if an SQLEXCEPTION happens, but to make sure that the code right after the continue handler is executed before processing continues. In my function, I am instructing the AS/400 that if an error occurs, I want the variable F_TEST to be set to the value 1. On the next line of the function, I am setting F_TEST to 0. F_TEST will only contain the value of 1 if the continue handler is fired.

The next line attempts to convert the passed-in argument to a DB2 date and place the results into the F_OUTPUT variable. I then test the variable F_TEST to see if an error occurred during the conversion. If the conversion was successful, I return the converted data; otherwise I return a null value. And that is the end of the function.

Once the function is placed on your system, you use it in the following manner:

SELECT  JDDCONV(MyField) FROM MyFile

MyField is the JD Edwards date column you want to convert. 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 = MyFuncLib

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

“But I’m not on V5,” you say. Or, “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. My friend, Domenic, has an AS/400, but he is on V4R5 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 I then compiled it on my system. I made a save file into which I saved the service program. I then sent it to my friend, and he restored the service program to his machine.

Bang, Domenic 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 technique 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 perhaps 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 on my Web site a save file containing this user-defined function, complete with detailed instructions on how to restore it to your AS/400. In a nutshell, simply 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. Bang, now you can use the JDDCONV 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 at www.sqlthing.com/books . Or, visit Howard’s Web site to learn more about user-defined functions on the AS/400.

This Issue Sponsored By

Table of Contents

Content archive

Recent Posts

Subscribe

Pages

Search