比如你使用 SELECT DATE("20000229")- 1 UNITS YEAR FROM dual ,从“2000/02/29”往前推算1年,而1999年,不是闰年,其2月份只有28天,这时就会报错(错误号1267)(出错信息:1267: The result of a datetime computation is out of range.) 其它比如你从7月31日往前推1月,从1月31日/1月30日往后推1月,都会碰到日期边界处理出错的问题,给项目开发带来一些困扰!
ON EXCEPTION SET esql, eisam, etext IF esql < 0 THEN RAISE EXCEPTION esql, eisam, etext; END IF END EXCEPTION
LET tmp_random_date = v_random_date; LET rtndate = NULL; LET v_pass1 = 0; IF v_mode = 'H' THEN LET v_pass = v_pass*6; ELIF v_mode = 'Q' THEN LET v_pass = v_pass*3; END IF; { SET DEBUG FILE TO "random.log"; TRACE ON; } WHILE 1 = 1 ON EXCEPTION SET esql IF esql = -1267 THEN LET tmp_random_date = tmp_random_date -1; ELIF esql = -1263 THEN --处理ONLINE7.3x BUG: 2004/02/20 -14(或15) UNITS MONTH出错 LET v_pass1 = v_pass1 + 1; ELIF esql = -1210 THEN --处理ONLINE7.3x BUG: 2004/02/20 -14(或15) UNITS MONTH出错 LET v_pass1 = v_pass1 + 1; ELSE RAISE EXCEPTION esql; END IF; END EXCEPTION;
IF v_mode = 'Y' THEN LET rtndate = tmp_random_date+v_pass UNITS YEAR; ELIF v_mode = 'D' THEN LET rtndate = tmp_random_date+v_pass UNITS DAY; ELSE -- v_mode IN ('M','H','Q') LET rtndate = (tmp_random_date+(v_pass-v_pass1) UNITS MONTH)+ v_pass1 UNITS MONTH; END IF EXIT WHILE; END WHILE; RETURN rtndate; END PROCEDURE;