DB2 Date to JD Edwards Conversion Function

The following documents the REALTOJD 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 REALTOJDS.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(WEBOE) DEV(*SAVF) SAVF(lib/REALTOJDS)
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 REALTOJDS(MyDateColumn) 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 WEBOE.REALTOJD
( RD DATE )
RETURNS DECIMAL(6,0)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
EXTERNAL ACTION
DISALLOW PARALLEL
SET OPTION TGTRLS=V4R4M0
/*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

Follow this link for information about Howard's book, iSeries and AS/400 SQL at Work.