It is well known that fields that are formatted as true dates can be easily compared and manipulated. For instance, getting days between two dates is easy with the DAYS() function. Also, it is easy to add or subtract a certain period of time with SQL. More on date functions within SQL can be found here.
However, when passing a date to an external object for some comparison or other function, one must be sure to follow these steps in order to successfully pass a date field to the external object.
- First, we must create the external SQL object. For more on this, please see this document. The most important part of this, is to add the parameter as a char(10). Then, declare a variable of type date. Finally, set your new variable of type date to the parameter brought in of type char(10) (see code below).
- Now, you can use this new date variable to perform any comparisons or manipulations needed in your function.
- After creating this procedure, open m-Power and create a new external object. Fill out the appropriate fields and register your parameters, being sure to set your date parameter as length of 10.
- Register the external object to your app as normal and compile.
Now, at run time, any true dates will be submitted and compared as normal.
Here is some sample code to help illustrate the CREATE PROCEDURE explained above:
CREATE PROCEDURE MRCWORKLIB.TEST
(INOUT i_error CHAR(99),
INOUT i_date CHAR(10))
LANGUAGE SQL
IS DETERMINISTIC
MODIFIES SQL DATA
BEGIN
DECLARE TRUDATE DATE;
SET TRUDATE = DATE(i_date);
CASE
WHEN something happens THEN
set i_error = ‘Some error.’;
ELSE
set i_error = ”;
END CASE;
END