Oracle time format conversion Exception Handling Function

Source: Internet
Author: User

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;
  49. IF TO_NUMBER (M)> 12THEN
  50. M: ='12';
  51. ELSIF TO_NUMBER (M) <1THEN
  52. M: ='01';
  53. ENDIF;
  54. IF TO_NUMBER (D) <1THEN
  55. D: ='01';
  56. ELSIF TO_NUMBER (D)> 28THEN
  57. -- Get the last day of the month
  58. SELECTTO_CHAR (last_day (to_date (Y | M |'01','Yyyy-mm-dd')),'Yyyymmdd')INTOV_LAST_DATEFROMDUAL;
  59. IF TO_NUMBER (SUBSTR (V_LAST_DATE, 7,2) <TO_NUMBER (D)THEN
  60. D: = SUBSTR (V_LAST_DATE, 7,2 );
  61. ENDIF;
  62. ENDIF;
  63. IF TO_NUMBER (H)> 23THEN
  64. H: ='23';
  65. ELSIF TO_NUMBER (H) <1THEN
  66. H: ='01';
  67. ENDIF;
  68. IF TO_NUMBER (MI)> 59THEN
  69. H: ='59';
  70. ELSIF TO_NUMBER (MI) <0THEN
  71. MI: ='00';
  72. ENDIF;
  73. IF TO_NUMBER (S)> 59THEN
  74. S: ='59';
  75. ELSIF TO_NUMBER (S) <0THEN
  76. S: ='00';
  77. ENDIF;
  78. Str_date: = Y | M | D | H | MI | S;
  79. -- Dbms_output.put_line (str_date );
  80. SelectTo_date (str_date,'Yyyymmddhh24miss')IntoV_endDateFromDual;
  81. ReturnV_endDate;
  82. EXCEPTION
  83. WHENOTHERS
  84. THEN
  85. Return '';
  86. End;
  87. End;
Example: to_date (XXX, 'yyyy-mm-dd ')

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.