JD Edwards Date Conversion Function

The following documents the JDDCONV user defined function and how to implement it on your AS/400. Contact hfarner@sqlthing.com if you have any questions regarding this function or it's installation. As always, please remember that we are available for consulting with your data access issues, projects and problems.

Steps to install the function

First, you need to download the save file from this link. Remember the location on your PC where you saved the file.

Next, go to a command prompt and change to the directory where you placed the downloaded save file using the CD command. Start FTP by typing FTP xxx.xxx.xxx.xxx where and press enter. The xxx represents the IP address of your AS/400. When prompted, enter your user ID and password.

At the FTP prompt, type CD <libname> and press enter, where <libname> is the name of the library where you want to put the save file.

Next, type Binary and press enter to place the FTP session into binary transfer mode.

Type PUT JDEFUN.SAVF and press enter. The save file will be transferred to your AS/400.

Next, start up an AS/400 terminal session and execute the following command:

RSTOBJ OBJ(*ALL) SAVLIB(MCEIS) DEV(*SAVF) SAVF(lib/JDEFUN)
MBROPT(*ALL) ALWOBJDIF(*ALL) RSTLIB(tgtlib)

Remember that you need to replace lib with the name of the library you sent the save file to and tgtlib with the name of the library you want to restore the function to. Once the restore is complete, the user defined function is ready to use. To test is, either download SQLThing or use STRSQL to get an interactive SQL session. Then, type a simple SQL statement like:

SELECT JDDCONV(mycolumn) FROM mytable

If the function is in your library list, the SQL statement should execute and you will see the results. If you receive an error it means that the function library is not your current library. Try typing the following:

SET CURRENT FUNCTION PATH = lib

In the above statement, lib should be replaced with the name of the library you restored the user defined function to. The function should now be usable in your SQL environment.

Code for the Function

The following is the code for the UDF.

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