Pesky Date Conversions with a UDF

Hey, Howard:

I've read some of your articles on how to create a UDF (user-defined function), and they are very helpful. I have tried it by creating several Oracle-like functions, like LPAD and TO_CHAR; however, I haven't been able to create TO_DATE successfully.

Could you help me find the problem in the attached function? It seems that the format of the character string is not accepted by DATE. I tried DATE('2003-10-15') on STRSQL, and it works. I'm using V4R5.

--Jonathan

Glad to hear some of the previous articles were helpful. Let me start by showing your function to our readers. I then will address the problem you are having and offer several solutions.

Jonathan wants to replicate an Oracle function that takes two arguments, a string representation of a date and a representation of the date format. The function should then return a date data type representation of the supplied string. Here is Jonathan's code for the function:

CREATE FUNCTION SQLBOOK.TO_DATE(inpdat VARCHAR(10), fmt VARCHAR(10)) 
RETURNS DATE 
LANGUAGE SQL 
READS SQL DATA 
NO EXTERNAL ACTION 
BEGIN 
DECLARE outdat DATE; 
DECLARE str CHAR(10); 
DECLARE tmp VARCHAR(10); 
SET tmp = TRIM(inpdat); 
CASE TRIM(fmt) 
       WHEN 'YYYYMMDD' THEN 
       SET str = SUBSTR(tmp,1,4) CONCAT '-' CONCAT 
       SUBSTR(tmp,5,2) CONCAT '-' CONCAT SUBSTR(tmp,7,2); 
ELSE 
       SIGNAL SQLSTATE '38Z01' 
       SET MESSAGE_TEXT = 'INVALID FORMAT SPECIFIED.'; 
       END CASE; 
SET outdat = DATE(str); 
RETURN outdat; 
END 

The above code should work, but when it was entered, via STRSQL, and then tested, the program returned nulls for valid dates instead of converting the date. That's because the DATE function is influenced by the date format that the program is created in. So the above function is relying on the fact that the program is compiled using *ISO date formats (note that the function is concatenating an *ISO format date string). Now, say you start the STRSQL program and press F13 and change your date format to *MDY and issue the create function statement above. When you attempt to use the function in a select statement, the function will return the null value for any date you supply, even if it is a valid date, because the program is compiled using the *MDY format, and this causes the DATE scalar function to want incoming date data to be in *MDY format. If you drop the function and then change your session to *ISO and re-create the function, the function will work correctly.

If you are using a program like SQLThing to edit your procedures and functions, the date settings are controlled by the format selection you choose when setting up your ODBC data source. In Client Access Express, the date format is controlled by the JDBC Setup menu item, under the Connection menu in the Run SQL Scripts program.

If you are using V5R1 or later, you can issue a set option statement in the function declaration to set the date format that should be used when the function is compiled. Here is the code modified to use the set option statement (the set option statement in highlighted in red):

CREATE FUNCTION SQLBOOK.TO_DATE(
inpdat VARCHAR(10), fmt VARCHAR(10)) 
RETURNS DATE 
LANGUAGE SQL 
READS SQL DATA 
NO EXTERNAL ACTION 
SET OPTION datfmt=*ISO
BEGIN 
DECLARE outdat DATE; 
DECLARE str CHAR(10); 
DECLARE tmp VARCHAR(10); 
SET tmp = TRIM(inpdat); 
CASE TRIM(fmt) 
       WHEN 'YYYYMMDD' THEN 
       SET str = SUBSTR(tmp,1,4) CONCAT '-' CONCAT 
       SUBSTR(tmp,5,2) CONCAT '-' CONCAT SUBSTR(tmp,7,2); 
       ELSE 
       SIGNAL SQLSTATE '38Z01' 
       SET MESSAGE_TEXT = 'INVALID FORMAT SPECIFIED.'; 
END CASE; 
SET outdat = DATE(str); 
RETURN outdat; 
END

Using the SET OPTION is the best way to control this, as it overrides whatever setting you may have in STRSQL or in the client tool you are using to issue the create function statement. However, you must be on V5R1 to use the SET OPTION statement in a function declaration. Once the function is created and the correct date option is used in the compilation of the function, the function will work correctly regardless of the date option set chosen by any client connection.

Howard F. Arner, Jr., is a writer and consultant with Client Server Development, and author of iSeries and AS/400 SQL at Work. Howard also designed SQLThing Enterprise Edition, a query program and stored procedure editor specifically for the AS/400's unique capabilities. You can purchase a copy of Howard's book or learn more about SQLThing at www.sqlthing.com. Send your SQL and scripting questions to harner@sqlthing.com or go to www.sqlthing.com to find out more about SQL on the iSeries.

Sponsored By ADVANCED SYSTEMS CONCEPTS

WHAT IF Creating Queries, Reports and Downloads was Easy, Fast and Convenient for ALL Users? - - - Make it happen with SEQUEL ! - - - Get a Free Trial today. Read More