-- You may import this sql script-file by doing:
-- db2 -td@ -f <filename>

-- Author: Troels Arvin <troels@arvin.dk>
-- No guarantees or warranties made. Use this on your own risk.

DROP FUNCTION cast2date_ornull RESTRICT@
DROP FUNCTION normstr_date RESTRICT@
DROP FUNCTION chkstr_date RESTRICT@
DROP PROCEDURE chkstr_date_ RESTRICT@

-- Helper function for the check_date and cast2date_ornull functions.
-- If the language supported private functions, this function would
-- be a private function; hence the _ in the end of the procedure
-- name.
CREATE PROCEDURE chkstr_date_(IN strval VARCHAR(254))
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN
    DECLARE retval INT DEFAULT 1;
    DECLARE err_state CHAR(5) DEFAULT '00000';
    DECLARE SQLSTATE  CHAR(5) DEFAULT '00000';
    DECLARE dateval DATE;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
        BEGIN
            SET err_state=SQLSTATE;

            IF      err_state='22007' THEN SET retval = 0;
            ELSEIF  err_state='22008' THEN SET retval = 0;
            ELSE    RESIGNAL SQLSTATE err_state;
            END IF;
        END
    ;
    IF strval IS NULL THEN SET retval=NULL;
    ELSE
        SET dateval=DATE(strval);
        RETURN retval;
    END IF;
END@
GRANT EXECUTE ON PROCEDURE chkstr_date_ TO PUBLIC@

COMMIT@

-- Returns 1 on a valid date string.
-- Returns 0 on an invalid date string.
-- Returns NULL if the input string is NULL.
-- The input value is expected to be of the form YYYY-MM-DD,
-- so you may have to run the value through "normstr_date"
-- before calling this function.
CREATE FUNCTION chkstr_date(strval VARCHAR(254))
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
    DECLARE retval INTEGER;
    IF strval IS NULL THEN SET retval=NULL;
    ELSE
        CALL chkstr_date_(strval);
        GET DIAGNOSTICS retval = DB2_RETURN_STATUS;
    END IF;
    RETURN retval;
END@
GRANT EXECUTE ON FUNCTION chkstr_date TO PUBLIC@

-- Outputs a string in the form of YYYY-MM-DD by heuristics on the
-- input value. E.g., if input is 20060101, the function returns
-- 2006-01-01.
CREATE FUNCTION normstr_date(in_val VARCHAR(254))
RETURNS CHAR(10)
NO EXTERNAL ACTION
DETERMINISTIC
LANGUAGE SQL
CONTAINS SQL
RETURN
CASE
     WHEN LENGTH(RTRIM(in_val))=10 AND in_val LIKE '____-__-__'      THEN
        RTRIM(in_val)
     WHEN LENGTH(RTRIM(in_val))>=8 AND in_val NOT LIKE '%-%'     THEN
        CHAR(SUBSTR(in_val,1,4)||'-'||SUBSTR(in_val,5,2)||'-'||SUBSTR(in_val,7,2))
     WHEN LENGTH(RTRIM(in_val))>=10 AND (in_val LIKE '____-__-__ %' OR in_val LIKE '____-__-__-%') THEN
        CHAR(SUBSTR(in_val,1,10))
     ELSE
        NULL
END@
GRANT EXECUTE ON FUNCTION normstr_date TO PUBLIC@

-- Convert a string to a date; if conversion fails, return NULL.
-- The date format must be YYYY-MM-DD. In other words, this function
-- only checks that the date is valid (e.g. not '2006-03-44').
CREATE FUNCTION cast2date_ornull(strval VARCHAR(254))
RETURNS DATE
DETERMINISTIC
NO EXTERNAL ACTION
READS SQL DATA
BEGIN ATOMIC
    DECLARE callval INTEGER;
    DECLARE retval DATE;
    -- The following is needed because declaration of SQLEXECPTION handlers
    -- isn't allowed in UDFs:
    IF strval IS NULL THEN SET retval=NULL;
    ELSE
        CALL chkstr_date_(strval);
        GET DIAGNOSTICS callval = DB2_RETURN_STATUS;
        IF callval = 1 THEN SET retval=DATE(strval);
        ELSE SET retval=NULL;
        END IF;
    END IF;
    RETURN retval;
END@
GRANT EXECUTE ON FUNCTION cast2date_ornull TO PUBLIC@
