I showed you how to convert a DECIMAL(7,0) date value into an AS/400 Date field using SQL. In this article, I will take that one step further and create a user-defined function, (UDF), that will return a date from the legacy date field. The advantage of a UDF is that it simplifies your client side SQL and encapsulates the date conversion logic on the server.

First, lets review the situation. The client has a table which contains a log number and a DECIMAL(7,0) field that contains a representation of a date. The date is encoded as CYYMMDD where C is 0 for 1900 and 1 for 2000. The following SQL Statements will create the table in a library called WEBOE and will insert the test data into the tables.

CREATE TABLE weboe.h1t2
	(log DECIMAL(5,0),
	 ymd DECIMAL(7,0));

INSERT INTO weboe.h1t2 VALUES (1096,821231);
INSERT INTO weboe.h1t2 VALUES (1461,831231);
INSERT INTO weboe.h1t2 VALUES (2192,851231);
INSERT INTO weboe.h1t2 VALUES (7305,991231);
INSERT INTO weboe.h1t2 VALUES (7306,1000101);
INSERT INTO weboe.h1t2 VALUES (10228,1080101);

The formula for creating a date from the YMD field value is the following SQL expression:

DATE(
	SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘||
	SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘||
	SUBSTR(CHAR(19000000 + YMD),1,4)
	)

Again, putting this in an SQL statement is a real bear, especially if you have several dates that you want to convert in a single statement or if you want to do date manipulations with the resultant data. The following UDF, written in PSM language, can be executed on a V4R5/V5R1 system to create a user-defined function that will convert the legacy date into an SQL date. Hint, SQLThing Enterprise Edition has a fantastic color function syntax editor that you could use to enter and compile this code.

CREATE FUNCTION WEBOE.CONVDATE
	(I_YMD DECIMAL(7,0))
  RETURNS DATE
  LANGUAGE SQL
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(
          SUBSTR(CHAR(19000000 + I_YMD),5,2) ||'/'||
          SUBSTR(CHAR(19000000 + I_YMD),7,2) ||'/'||
          SUBSTR(CHAR(19000000 + I_YMD),1,4)
        );
  IF F_TEST=0 THEN
       RETURN F_OUTPUT;
  ELSE
       RETURN NULL;
  END IF;
END

This function takes as input a DECIMAL(7,0) field. As output, it returns an SQL DATE value. Note that I have placed a continue handler to grab any SQL exception and set the variable F_TEST equal to 1. If I have bum values in the input to the function, the exception handler will cause F_TEST to be set to 1 and execution of the UDF will continue. On the line after the date conversion, I look at the value of F_TEST. If F_TEST is 0, then the date conversion was successful and I return the value of the F_OUTPUT field, (which contains the date). If the conversion was unsuccessful, I return a NULL. In this manner, my function will execute correctly even if the input data is bad and not cause an exception.

To put the function on your machine, you can execute the above code in STRSQL, or if you have a copy of SQLThing Enterprise Edition, you can use the Procedure/Function editor to enter the code and create the function. Once the function is created on your AS/400, the following SQL statement will work against your AS/400.

SELECT LOG, YMD, WEBOE.CONVDATE(YMD) 
	FROM WEBOE.H1T2;

Notice that I qualified the name of the user defined function with the library where it lives. This is so that SQL can find the user defined function. To avoid having to qualify my user-defined function, I could execute the following statement prior to using my new user defined function:

SET PATH=WEBOE;

The above statement causes the AS/400 to search for any user defined functions in the WEBOE library. Note that the argument to PATH can be a list of libraries, so the following would be valid:

SET PATH=HD1100PD, WEBOE;

The above statement causes the AS/400 to search for user-defined functions first in the HD1100PD library, and then in the WEBOE library.

Executing the SQL statement above against the test table will cause the following output to appear:

Now that I have the user-defined function, my SQL statements can be a little easier to read and I can manipulate the numeric date fields quite easily. Here is an example statement where I do a number of manipulations against the output of the function:

SELECT log, ymd,convdate(ymd) AS DATE,
	dayofweek(convdate(ymd)) AS DAYOFWEEK,
	quarter(convdate(ymd)) AS QUARTER,
	week(convdate(ymd)) AS WEEK
  FROM weboe.h1t2

The output of the above statement should look like this:

User-defined functions can be great for simplifying statements on placing business logic on the server.

-Howard