JD Edwards Date Conversion Function
The following documents the JDDCONV user defined
function and how to implement it on your AS/400. Contact email@example.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
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
Code for the Function
The following is the code for the UDF.
CREATE FUNCTION MCEIS.JDDCONV
SET OPTION DATFMT=*ISO
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;