Oracle学习笔记_10_判断是否为日期类型

FUNCTION isdate (datestr VARCHAR2, format VARCHAR2) RETURN number IS

    p_date   DATE;

BEGIN

    SELECT TO_DATE (datestr, format)

    INTO p_date

    FROM DUAL;

    RETURN 1;

EXCEPTION

    WHEN OTHERS  THEN

       RETURN 0;

END;

多条件模糊查询时：

function get_date_str ( p_date varchar2) return varchar2 is

     v_date   date;

  begin

     if ( length(p_date) = 4 ) then

         select to_date (p_date, 'yyyy')

         into v_date

         from dual;

         return to_char( v_date ,'yy');

     elsif ( length(p_date) = 6 )then

         select to_date (p_date, 'yyyymm')

         into v_date

         from dual;

         return to_char( v_date ,'mm')  || '月-' ||  to_char( v_date,'yy');

     elsif ( length(p_date) = 7 )then

         select to_date (p_date, 'yyyy-mm')

         into v_date

         from dual;

         return to_char( v_date ,'mm')  || '月-' ||  to_char( v_date,'yy');             

     elsif ( length(p_date) = 8 ) then

         select to_date (p_date, 'yyyymmdd')

         into v_date

         from dual;

         return  to_char(v_date,'dd') || '-' || to_char( v_date,'mm')  || '月-' ||  to_char(v_date,'yy');

     elsif ( length(p_date) = 10 ) then

         select to_date (p_date, 'yyyy-mm-dd')

         into v_date

         from dual;

         return  to_char(v_date,'dd') || '-' || to_char( v_date,'mm')  || '月-' ||  to_char(v_date,'yy');

     end if;

     return '11-00月-00';

  exception

    when others then

       return '00-00月-00';

  end get_date_str;

附录：参考资料

1.oracle中判断是否为日期/number格式

巴特西

Oracle学习笔记_10_判断是否为日期类型

附录：参考资料

最新文章

热门文章