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 firstname.lastname@example.org
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
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
Code for the Function
The following is the code for the UDF.
CREATE FUNCTION WEBOE.REALTOJD
( RD DATE )
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION TGTRLS=V4R4M0
/*Begin Function Code*/
DECLARE F_OUTPUT DECIMAL ( 6 , 0 ) ;
SET F_OUTPUT = DECIMAL ( ( YEAR ( RD ) - 1900 ) * 1000 +
DAYOFYEAR ( RD ) , 6 ) ;
RETURN F_OUTPUT ;
Follow this link for information about
Howard's book, iSeries and AS/400 SQL at Work.