oracle查询年月是否正确
–depYear年是否在1901和2022之间
–depdate月的值是否在01和12之间
select * from dep_info where
not (substr(depYear,1,4) between '1901' and '2022' and
substr(depdate,6,2) between '01' and '12')
–查询日期是否合法(比如2021-02-29,本年2月只有28号)
CREATE OR REPLACE FUNCTION IS_DATE(str VARCHAR2)
RETURN NUMBER
IS
tmpDT DATE;
BEGIN
tmpDT := TO_DATE(NVL(str, 'kk'), 'YYYY-MM-DD HH24:MI:SS');
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
RETURN 0;
END;
–不合法的是0
select * from deployee_info t where IS_DATE(t.createdate) = 0