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

發布時間: 2012/8/26 15:21:43

-

Oracle時間格式轉換異常處理函數
1.create or replace function F_FORMAT_DATE(v_date varchar2,format varchar)
2./*
3.時間格式處理格式
4.*/
5.return date
6.as
7.       v_endDate date;
8.       v_rn varchar2(2);
9.       V_LAST_DATE VARCHAR2(10);
10.       Y VARCHAR2(4);
11.       M VARCHAR2(4);
12.       D VARCHAR2(4);
13.       H VARCHAR2(4);
14.       MI VARCHAR2(2);
15.       S VARCHAR2(2);
16.       M_count number;
17.       str_date varchar2(20);
18.begin
19.       str_date:=v_date;
20.       SELECT TO_CHAR(str_date) INTO v_endDate FROM DUAL;
21.       RETURN v_endDate;
22.       EXCEPTION
23.       WHEN OTHERS THEN
24.         begin
25.         IF v_date IS NULL THEN
26.             RETURN '';
27.         END IF;
28.         IF LENGTH(TRIM(v_date))<4 THEN
29.             RETURN '';
30.         END IF;
31.         IF LENGTH(str_date)<14 THEN
32.            str_date:=rpad(trim(v_date),14,'0');
33.         ELSIF LENGTH(str_date)>14 THEN
34.            str_date:=substr(str_date,1,14);
35.         END IF;
36.         Y:=SUBSTR(str_date,1,4);
37.         M:=SUBSTR(str_date,5,2);
38.         D:=SUBSTR(str_date,7,2);
39.         H:=SUBSTR(str_date,9,2);
40.         MI:=SUBSTR(str_date,11,2);
41.         S:=SUBSTR(str_date,13,2);
42.         SELECT lengthb(m) into M_COUNT FROM DUAL;
43.         IF M_COUNT>2 THEN
44.            select to_date(str_date) into v_endDate from dual;
45.            return v_endDate;
46.         END IF;
47.         --獲取當年是否潤年
48.         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;
49.
50.        IF TO_NUMBER(M)>12 THEN
51.           M:='12';
52.        ELSIF TO_NUMBER(M)<1 THEN
53.           M:='01';
54.        END IF;
55.        IF TO_NUMBER(D)<1 THEN
56.           D:='01';
57.        ELSIF TO_NUMBER(D)>28 THEN
58.           --獲取當月的最后一天
59.           SELECT TO_CHAR(last_day(to_date(Y||M||'01','yyyy-mm-dd')),'YYYYMMDD') INTO V_LAST_DATE FROM DUAL;
60.           IF TO_NUMBER(SUBSTR(V_LAST_DATE,7,2))<TO_NUMBER(D) THEN
61.              D:=SUBSTR(V_LAST_DATE,7,2);
62.           END IF;
63.        END IF;
64.        IF TO_NUMBER(H)>23 THEN
65.           H:='23';
66.        ELSIF TO_NUMBER(H)<1 THEN
67.           H:='01';
68.        END IF;
69.        IF TO_NUMBER(MI)>59 THEN
70.           H:='59';
71.        ELSIF TO_NUMBER(MI)<0 THEN
72.           MI:='00';
73.        END IF;
74.        IF TO_NUMBER(S)>59 THEN
75.           S:='59';
76.        ELSIF TO_NUMBER(S)<0 THEN
77.           S:='00';
78.        END IF;
79.        str_date:=Y||M||D||H||MI||S;
80.        --dbms_output.put_line(str_date);
81.        select to_date(str_date,'YYYYMMDDHH24MISS') into v_endDate from dual;
82.        return v_endDate;
83.        EXCEPTION
84.           WHEN OTHERS
85.           THEN
86.              return '';
87.        end;
88.end; 例如：to_date(XXX,‘yyyy-mm-dd’)

本文出自：億恩科技【www.endtimedelusion.com】

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]