/* Formatted on 2008/11/21 11:58 (Formatter Plus v4.8.8) */ CREATE OR REPLACE FUNCTION valid_date (in_date VARCHAR2) RETURN VARCHAR2 IS flag VARCHAR2 (10); BEGIN SELECT DECODE ( ADD_MONTHS (TRUNC (TO_DATE (in_date, 'yyyymmdd'), 'yyyy'), 12 ) - TRUNC (TO_DATE (in_date, 'yyyymmdd'), 'yyyy'), 366, '闰年', '平年' ) INTO flag FROM DUAL; RETURN flag; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN '错误日期'; WHEN OTHERS THEN RETURN '错误日期'; END valid_date; /select valid_date('200808121') from dual '错误日期'select valid_date('20080812') from dual '闰年'
- TRUNC (SYSDATE, 'yyyy'),
366, '闰年',
'平年'
)
FROM DUAL
CREATE OR REPLACE FUNCTION valid_date (in_date VARCHAR2)
RETURN VARCHAR2
IS
flag VARCHAR2 (10);
BEGIN
SELECT DECODE ( ADD_MONTHS (TRUNC (TO_DATE (in_date, 'yyyymmdd'), 'yyyy'),
12
)
- TRUNC (TO_DATE (in_date, 'yyyymmdd'), 'yyyy'),
366, '闰年',
'平年'
)
INTO flag
FROM DUAL; RETURN flag;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN '错误日期';
WHEN OTHERS
THEN
RETURN '错误日期';
END valid_date;
/select valid_date('200808121') from dual
'错误日期'select valid_date('20080812') from dual
'闰年'
'yyyymmdd'这种格式
还是需要判断
像
'yyyy-mm-dd','yyyy/mm/dd','yyyy-mon-dd'之类的格式全算正确?
不用考虑闰年,to_date已经帮你做了.
我们项目中就是这么做的...也就3,4行代码的一个function.