Oracle時間格式轉換異常處理函數

 

create or replace function F_FORMAT_DATE(v_date varchar2,format varchar)/*時間格式處理格式*/return dateas       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’)