我建议你在输入的时候做验证,to_date()函数能够识别不正常的字符串,你当然也可以在查询的select语句后面进行异常处理,异常可以如下:
Exception
when no_data_found then
str_dis:=null;
when others then
str_dis:=to_date('1900-1-1','yyyy-mm-dd')str_dis是你准备赋值的显示字段
Exception
when no_data_found then
str_dis:=null;
when others then
str_dis:=to_date('1900-1-1','yyyy-mm-dd')str_dis是你准备赋值的显示字段
create or replace function test_fun(p_date varchar2,p_type varchar2) return date
as
p_outdate date;
begin
p_outdate:=to_date(p_date,p_type);
return p_outdate;
exception
when others then
p_outdate:=to_date('1900-01-01','yyyy-mm-dd'); --出错返回1900-01-01
return p_outdate;
end;
QL> select * from testa; ID NAME
--------- -------------------------------------------
1 2004-08-01
2 2004-88-01SQL> select test_fun(a.name,'yyyy-mm-dd') from testa a;TEST_FUN(A.NAME,'YYYY-MM-DD')
-----------------------------
2004-8-1
1900-1-1 select fun_test(付款日期字段,'yyyy-mm-dd') from aTable;
v_date date;
begin
v_date:=to_date(str,'yyyymmdd');
exception when others then
v_date:=to_date('19000101','yyyymmdd');
end;