Oracle Learning Note _10_ to determine if it is a date type

FUNCTION IsDate(DatestrVARCHAR2, formatVARCHAR2)RETURN  Number  isp_date date;BEGIN    SELECTto_date (DATESTR, format) intop_date fromDUAL; RETURN 1; EXCEPTION whenOTHERS Then       RETURN 0;END;

When you have a multi-conditional fuzzy query:

functionGet_date_str (p_datevarchar2)return varchar2  isv_date date; begin     if(Length (p_date)= 4) Then         SelectTo_date (P_date,'yyyy')          intov_date fromdual; returnTo_char (V_date,'yy'); elsif (Length (p_date)= 6) Then         SelectTo_date (P_date,'yyyymm')          intov_date fromdual; returnTo_char (V_date,'mm')|| 'Month-' ||To_char (V_date,'yy'); elsif (Length (p_date)= 7) Then         SelectTo_date (P_date,'yyyy-mm')          intov_date fromdual; returnTo_char (V_date,'mm')|| 'Month-' ||To_char (V_date,'yy'); elsif (Length (p_date)= 8) Then         SelectTo_date (P_date,'YYYYMMDD')          intov_date fromdual; returnTo_char (V_date,'DD')|| '-' ||To_char (V_date,'mm')|| 'Month-' ||To_char (V_date,'yy'); elsif (Length (p_date)= Ten) Then         SelectTo_date (P_date,'YYYY-MM-DD')          intov_date fromdual; returnTo_char (V_date,'DD')|| '-' ||To_char (V_date,'mm')|| 'Month-' ||To_char (V_date,'yy'); End if; return 'November-00月 on -00'; Exception whenOthers Then       return '00月-00月 on -00'; EndGET_DATE_STR;

View Code

Appendix: References

Determine if the date/number format is in 1.oracle

Oracle Learning Note _10_ to determine if it is a date type