oracle - 将许多日期格式转换为单个格式化日期

I tried to delete all special characters and after that use a function to transform that string into a single format of date. My function return more exceptions, I don't know why...

功能:

CREATE OR REPLACE FUNCTION normalize_date (data_in IN VARCHAR2)
    RETURN DATE
IS
    tmp_month         VARCHAR2 (3);
    tmp_day           VARCHAR2 (2);
    tmp_year          VARCHAR2 (4);
    TMP_YEAR_NUMBER   NUMBER;
    result            DATE;
BEGIN
    tmp_day := SUBSTR (data_in, 1, 2);
    tmp_year := SUBSTR (data_in, -4);

    --if(REGEXP_LIKE(SUBSTR(data_in,3,2), '[:alpha:]')) then 
    if(SUBSTR(data_in,3,1) in ('a','j','i','f','m','s','o','n','d','A','J','I','F','M','S','O','N','D')) then      
    tmp_month := UPPER(SUBSTR (data_in, 3, 3));
    else
    tmp_month := SUBSTR (data_in, 3, 2);
    end if;

    DBMS_OUTPUT.put_line (tmp_year);

    TMP_YEAR_NUMBER := TO_NUMBER (tmp_year);

    IF (tmp_month = 'JAN')
    THEN
        tmp_month := '01';
    END IF;

    IF (tmp_month = 'FEB')
    THEN
        tmp_month := '02';
    END IF;

    IF (tmp_month = 'MAR')
    THEN
        tmp_month := '03';
    END IF;

    IF (tmp_month = 'APR')
    THEN
        tmp_month := '04';
    END IF;

    IF (tmp_month = 'MAY')
    THEN
        tmp_month := '05';
    END IF;

    IF (tmp_month = 'JUN')
    THEN
        tmp_month := '06';
    END IF;

    IF (tmp_month = 'JUL')
    THEN
        tmp_month := '07';
    END IF;

    IF (tmp_month = 'AUG')
    THEN
        tmp_month := '08';
    END IF;

    IF (tmp_month = 'SEP')
    THEN
        tmp_month := '09';
    END IF;

    IF (tmp_month = 'OCT')
    THEN
        tmp_month := '10';
    END IF;

    IF (tmp_month = 'NOV')
    THEN
        tmp_month := '11';
    END IF;

    IF (tmp_month = 'DEC')
    THEN
        tmp_month := '12';
        END IF;

   -- dbms_output.put_line(tmp_day || '~'||tmp_year || '~' ||tmp_month);

    IF (LENGTH (tmp_day || tmp_year || tmp_month) <> 8)
    THEN
        result := TO_DATE ('31122999', 'DDMMYYYY');
        RETURN result;
    END IF;

 --   dbms_output.put_line('before end');
    result:=TO_DATE (tmp_day || tmp_month ||tmp_year , 'DDMMYYYY');
 --   dbms_output.put_line('date result: '|| result);
    RETURN result;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        NULL;
    WHEN OTHERS
    THEN
        result := TO_DATE ('3012299', 'DDMMYYYY');
        RETURN result;
        RAISE;
END normalize_date;

用法

SELECT customer_no,
       str_data_expirare,
       normalize_date (str_data_expirare_trim) AS data_expirare_buletin
  FROM (SELECT customer_no,
               str_data_expirare,
               REGEXP_REPLACE (str_data_expirare, '[^a-zA-Z0-9]+', '')
                   AS str_data_expirare_trim
          FROM (SELECT Q1.set_act_id_1,
                       Q1.customer_no,
                       NVL (SUBSTR (set_act_id_1,
                                      INSTR (set_act_id_1,
                                             '+',
                                             1,
                                             5)
                                    + 1,
                                    LENGTH (set_act_id_1)),
                            'NULL')
                           AS str_data_expirare
                  FROM STAGE_CORE.IFLEX_CUSTOMERS Q1
                  WHERE Q1.set_act_id_1 IS NOT NULL
                  )
        );

推荐答案

如果您对所有可能的日期格式有一个合理的想法，那么使用蛮力可能会更容易:

create or replace function clean_date
    ( p_date_str in varchar2)
    return date
is
    l_dt_fmt_nt sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll
        ('DD-MON-YYYY', 'DD-MON-YY', 'DD-MM-YYYY', 'MM-DD-YYYY', 'YYYY-MM-DD'
         , 'DD/MM/YYYY', 'MM/DD/YYYY', 'YYYY/MM/DD', 'DD/MM/YY', 'MM/DD/YY');
    return_value date;
begin
    for idx in l_dt_fmt_nt.first()..l_dt_fmt_nt.last()
    loop
        begin
            return_value := to_date(p_date_str, l_dt_fmt_nt(idx));
            exit;
        exception
             when others then null;
        end;
    end loop;
    if return_value is null then
        raise no_data_found; 
    end if;
    return return_value;
exception
    when no_data_found then
        raise_application_error(-20000, p_date_str|| ' is unknown date format');
end clean_date;
/

SQL> select  clean_date('20160817') from dual;

CLEAN_DAT
---------
17-AUG-16

SQL> select  clean_date('160817') from dual;

CLEAN_DAT
---------
16-AUG-17

SQL> 

create or replace function clean_date
    ( p_date_str in varchar2)
    return date
is
    l_dt_fmt_nt sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll
        ('DD-MM-RR', 'MM-DD-RR', 'RR-MM-DD', 'RR-DD-MM'
         , 'DD-MM-YYYY', 'MM-DD-YYYY', 'YYYY-MM-DD', 'YYYY-DD-MM');
    return_value date;
begin
    for idx in l_dt_fmt_nt.first()..l_dt_fmt_nt.last()
    loop
        begin
            return_value := to_date(p_date_str, l_dt_fmt_nt(idx));
            exit;
        exception
             when others then null;
        end;
    end loop;
    if return_value is null then
        raise no_data_found; 
    end if;
    return return_value;
exception
    when no_data_found then
        raise_application_error(-20000, p_date_str|| ' is unknown date format');
end clean_date;
/