Oracle时间格式转换异常处理函数
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;
-
- 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’)
评论暂时关闭