CREATE TABLE TEST_DATE
(
DT INTEGER
);
INSERT INTO TEST_DATE VALUES(20080101);
INSERT INTO TEST_DATE VALUES(20080131);
INSERT INTO TEST_DATE VALUES(20080208);
INSERT INTO TEST_DATE VALUES(20080229);
INSERT INTO TEST_DATE VALUES(20080231);
INSERT INTO TEST_DATE VALUES(20081131);
INSERT INTO TEST_DATE VALUES(80327);
/* 建立一個user define function
input:字串
return:0 => 表示日期格式正確
-1 => 表示格示為非日期格式(挾雜非數字等...)
DROP FUNCTION FN_CHKVALIDATE
GO
CREATE FUNCTION FN_CHKVALIDATE (D VARCHAR(32))
RETURNS INTEGER
SPECIFIC FN_CHKVALIDATE
LANGUAGE SQL
BEGIN ATOMIC
DECLARE N INTEGER;
DECLARE i INTEGER DEFAULT 1;
-- length must = 8
IF LENGTH(RTRIM(D)) <> 8 OR RTRIM(COALESCE(D,'')) = '' THEN
RETURN -1;
END IF;
-- whole character should allow to turn into numbers
WHILE i <= LENGTH(RTRIM(COALESCE(D,''))) DO
IF ASCII(SUBSTR(RTRIM(D),i,1)) NOT BETWEEN 48 AND 57 THEN
RETURN -1;
END IF;
SET i = i + 1;
END WHILE;
-- month should be valid
IF INT(SUBSTR(D,1,4)) < 101 THEN
RETURN -1;
END IF;
IF INT(SUBSTR(D,5,2)) NOT BETWEEN 1 AND 12 THEN
RETURN -1;
END IF;
IF INT(SUBSTR(D,7,2)) < 1 THEN
RETURN -1;
END IF;
-- date is valid
IF (D > (SELECT CHAR(INT(DT))
FROM (SELECT DATE(SUBSTR(D,1,4) || '-' ||
SUBSTR(D,5,2) || '-01') +
1 MONTH - 1 DAY DT
FROM SYSIBM.SYSDUMMY1) T
)) THEN
SET N = -1;
ELSE
SET N = 0;
END IF;
RETURN N;
END
以TEST_DATA裡的DT欄位來測試FUNCTION是否WORK
SELECT USR_TO_DATE(CHAR(DT)),
FN_CHKVALIDATE(CHAR(DT))
FROM TEST_DATE
WHERE FN_CHKVALIDATE(CHAR(DT)) >= 0;
得到以下結果
2008/1/1 0
2008/1/31 0
2008/2/8 0
2008/2/29 0
(4 row(s) affected)
不使用function的結果
SELECT USR_TO_DATE(CHAR(DT))
FROM TEST_DATE;
得到以下結果
2008/1/1 0
2008/1/31 0
2008/2/8 0
2008/2/29 0
(4 row(s) affected)
Server Msg: -181, State: 22007, [IBM][CLI Driver][DB2/NT]
SQL0181N 日期時間值的字串表示式超出範圍 SQLSTATE=22007
