Oracle时间格式转换异常处理函数

 

create or replace function F_FORMAT_DATE(v_date varchar2,format varchar)
/*
时间格式处理格式
*/
return date
as
       v_endDate date;
       v_rn varchar2(2);
       V_LAST_DATE VARCHAR2(10);
       Y VARCHAR2(4);
       M VARCHAR2(4);
       D VARCHAR2(4);
       H VARCHAR2(4);
       MI VARCHAR2(2);
       S VARCHAR2(2);
       M_count number;
       str_date varchar2(20);  
begin  
       str_date:=v_date;
       SELECT TO_CHAR(str_date) INTO v_endDate FROM DUAL;
       RETURN v_endDate;
       EXCEPTION     
       WHEN OTHERS THEN
         begin 
         IF  v_date IS NULL THEN
             RETURN '';
         END IF;
         IF LENGTH(TRIM(v_date))<4 THEN
             RETURN '';
         END IF;
         IF LENGTH(str_date)<14 THEN
            str_date:=rpad(trim(v_date),14,'0'); 
         ELSIF  LENGTH(str_date)>14 THEN
            str_date:=substr(str_date,1,14);
         END IF; 
         Y:=SUBSTR(str_date,1,4);
         M:=SUBSTR(str_date,5,2);
         D:=SUBSTR(str_date,7,2);
         H:=SUBSTR(str_date,9,2);
         MI:=SUBSTR(str_date,11,2);
         S:=SUBSTR(str_date,13,2);
         SELECT  lengthb(m) into M_COUNT  FROM DUAL;
         IF M_COUNT>2 THEN  
            select to_date(str_date) into v_endDate from dual;
            return v_endDate;
         END IF;
         --获取当年是否润年
         select decode(to_char(last_day(trunc(to_date(substr(str_date,1,4)||'0101','yyyy-mm-dd'),'y')+31),'dd'),'29','rn','pn') into v_rn from dual;
        
        IF TO_NUMBER(M)>12 THEN
           M:='12';
        ELSIF TO_NUMBER(M)<1 THEN
           M:='01';
        END IF;
        IF TO_NUMBER(D)<1 THEN
           D:='01';         
        ELSIF TO_NUMBER(D)>28 THEN
           --获取当月的最后一天
           SELECT TO_CHAR(last_day(to_date(Y||M||'01','yyyy-mm-dd')),'YYYYMMDD') INTO V_LAST_DATE FROM DUAL;
           IF TO_NUMBER(SUBSTR(V_LAST_DATE,7,2))<TO_NUMBER(D) THEN
              D:=SUBSTR(V_LAST_DATE,7,2);    
           END IF;                                                                
        END IF;
        IF TO_NUMBER(H)>23 THEN
           H:='23';
        ELSIF TO_NUMBER(H)<1 THEN
           H:='01';
        END IF;
        IF TO_NUMBER(MI)>59 THEN
           H:='59';
        ELSIF TO_NUMBER(MI)<0 THEN
           MI:='00';
        END IF;
        IF TO_NUMBER(S)>59 THEN
           S:='59';
        ELSIF TO_NUMBER(S)<0 THEN
           S:='00';
        END IF;
        str_date:=Y||M||D||H||MI||S;
        --dbms_output.put_line(str_date);
        select  to_date(str_date,'YYYYMMDDHH24MISS') into v_endDate from dual;      
        return v_endDate; 
        EXCEPTION
           WHEN OTHERS
           THEN
              return '';
        end;
end;

例如：to_date(XXX,‘yyyy-mm-dd’)