Oracle time format conversion Exception Handling Function

Oracle time format conversion Exception Handling Function

1. Create Or Replace FunctionF_FORMAT_DATE (v_date varchar2, formatVarchar)
2. /*
3. Time Format processing format
4. */
5. Return Date
6. As
7. V_endDateDate;
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. SELECTTO_CHAR (str_date)INTOV_endDateFROMDUAL;
21. RETURNV_endDate;
22. EXCEPTION
23. WHENOTHERSTHEN
24. Begin
25. IF v_dateIS NULL THEN
26. RETURN '';
27. ENDIF;
28. If length (TRIM (v_date) <4THEN
29. RETURN '';
30. ENDIF;
31. If length (str_date) <14THEN
32. Str_date: = rpad (trim (v_date), 14,'0');
33. Elsif length (str_date)> 14THEN
34. Str_date: = substr (str_date, 1, 14 );
35. ENDIF;
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. SELECTLengthb (m)IntoM_COUNTFROMDUAL;
43. IF M_COUNT> 2THEN
44. SelectTo_date (str_date)IntoV_endDateFromDual;
45. ReturnV_endDate;
46. ENDIF;
47. -- Get whether the current year is renewed
48. SelectDecode (to_char (last_day (trunc (to_date (substr (str_date, 1, 4) |'123','Yyyy-mm-dd'),'Y') + 31 ),'Dd'),'29','Rn','Pn')IntoV_rnFromDual;
50. IF TO_NUMBER (M)> 12THEN
51. M: ='12';
52. ELSIF TO_NUMBER (M) <1THEN
53. M: ='01';
54. ENDIF;
55. IF TO_NUMBER (D) <1THEN
56. D: ='01';
57. ELSIF TO_NUMBER (D)> 28THEN
58. -- Get the last day of the month
59. SELECTTO_CHAR (last_day (to_date (Y | M |'01','Yyyy-mm-dd')),'Yyyymmdd')INTOV_LAST_DATEFROMDUAL;
60. IF TO_NUMBER (SUBSTR (V_LAST_DATE, 7,2) <TO_NUMBER (D)THEN
61. D: = SUBSTR (V_LAST_DATE, 7,2 );
62. ENDIF;
63. ENDIF;
64. IF TO_NUMBER (H)> 23THEN
65. H: ='23';
66. ELSIF TO_NUMBER (H) <1THEN
67. H: ='01';
68. ENDIF;
69. IF TO_NUMBER (MI)> 59THEN
70. H: ='59';
71. ELSIF TO_NUMBER (MI) <0THEN
72. MI: ='00';
73. ENDIF;
74. IF TO_NUMBER (S)> 59THEN
75. S: ='59';
76. ELSIF TO_NUMBER (S) <0THEN
77. S: ='00';
78. ENDIF;
79. Str_date: = Y | M | D | H | MI | S;
80. -- Dbms_output.put_line (str_date );
81. SelectTo_date (str_date,'Yyyymmddhh24miss')IntoV_endDateFromDual;
82. ReturnV_endDate;
83. EXCEPTION
84. WHENOTHERS
85. THEN
86. Return '';
87. End;
88. End;

Example: to_date (XXX, 'yyyy-mm-dd ')