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

Question

我想要将包含日期的字符串带到单个格式日期。例：

• 13-06-2012至13-6月-12
• 2012年6月13日至6月13日至12日
• 2012年6月13日至12日
• 2012年6月13日至6月13日至12日
• ..。

我尝试删除所有特殊字符，然后使用一个函数将该字符串转换为单一的日期格式。我的函数返回更多的异常，我不知道为什么.

职能：

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
                  )
        );

Answer 1

如果您对所有可能的日期格式有很好的了解，那么使用蛮力可能会更容易：

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;
/

请注意，现代版本的Oracle非常宽容日期转换。这个函数以列表中没有的格式处理日期，并带来一些有趣的结果：

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> 

这说明了在数据完整性规则松懈的情况下自动数据清理的局限性。罪恶的工资被数据破坏了。

@AlexPoole提出了使用'RR'格式的问题。日期掩码的这个元素是作为一个Y2K传说引入的。这是相当令人沮丧的，我们还在讨论它在新的千年将近20年。

问题是这个。如果我们把这个字符串'161225'转换到一个日期，它有几个世纪？好吧，'yymmdd'会给2016-12-15。很公平，但是'991225'呢？我们真正想要的日期有多可能是2099-12-15？这就是'RR'格式发挥作用的地方。基本上，它默认世纪:数字00-49默认为20，50-99默认为19。这个窗口由Y2K问题决定:在2000年，'98更有可能提到最近的过去，而不是不久的将来，类似的逻辑也适用于'02。因此，1950年的中点。注意，这是，不动点，而不是滑动窗口。随着我们离2000年越来越远，枢轴点就变得不太有用了。了解更多信息。

无论如何，关键是“RRRR”不能很好地处理其他日期格式：to_date('501212', 'rrrrmmdd') hurlsora-01843:不是有效的to_date('501212', 'rrrrmmdd') hurls。因此，我修改后的函数(经过一些整理)如下：

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;
/

关键仍然是:在解释日期的时候，我们的聪明程度是有限的，所以要确保你以最合适的方式来领导。如果您认为大多数日期字符串适合日、月、年，则应将其放在第一位；您仍然会得到一些错误的转换，但如果您使用年度月日进行引导，则会得到更少的转换。

Answer 2

字符串到日期转换规则允许附加格式规则(不应用任何其他修饰符)。(也请参阅这个问题)所以：

• MM还与MON和MONTH相匹配；
• MON也匹配MONTH (反之亦然)；
• YY还与YYYY相匹配；
• RR还与RRRR匹配；以及
• 标点符号是可选的。

这意味着你可以：

CREATE OR REPLACE FUNCTION parse_Date_String(
  in_string VARCHAR2
) RETURN DATE DETERMINISTIC
IS
BEGIN
  BEGIN
    RETURN TO_DATE( in_string, 'DD-MM-YY' );
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  BEGIN
    RETURN TO_DATE( in_string, 'MM-DD-YY' );
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  BEGIN
    RETURN TO_DATE( in_string, 'YY-MM-DD' );
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  RETURN NULL;
END;
/

查询

WITH dates ( value ) AS (
  SELECT '010101' FROM DUAL UNION ALL
  SELECT '02JAN01' FROM DUAL UNION ALL
  SELECT '03JANUARY01' FROM DUAL UNION ALL
  SELECT '04012001' FROM DUAL UNION ALL
  SELECT '05JAN2001' FROM DUAL UNION ALL
  SELECT '06JANUARY2001' FROM DUAL UNION ALL
  SELECT 'JAN0701' FROM DUAL UNION ALL
  SELECT 'JANUARY0801' FROM DUAL UNION ALL
  SELECT 'JAN0901' FROM DUAL UNION ALL
  SELECT 'JANUARY1001' FROM DUAL UNION ALL
  SELECT '990111' FROM DUAL UNION ALL
  SELECT '99JAN12' FROM DUAL UNION ALL
  SELECT '99JANUARY13' FROM DUAL UNION ALL
  SELECT '19990114' FROM DUAL UNION ALL
  SELECT '2001-01-15' FROM DUAL UNION ALL
  SELECT '2001JAN16' FROM DUAL UNION ALL
  SELECT '2001JANUARY17' FROM DUAL UNION ALL
  SELECT '20010118' FROM DUAL
)
SELECT value, parse_Date_String( value ) AS dt
FROM   dates;

输出

VALUE         DT
------------- -------------------
010101        2001-01-01 00:00:00
02JAN01       2001-01-02 00:00:00
03JANUARY01   2001-01-03 00:00:00
04012001      2001-01-04 00:00:00
05JAN2001     2001-01-05 00:00:00
06JANUARY2001 2001-01-06 00:00:00
JAN0701       2001-01-07 00:00:00
JANUARY0801   2001-01-08 00:00:00
JAN092001     2001-01-09 00:00:00
JANUARY102001 2001-01-10 00:00:00
990111        2099-01-11 00:00:00
99JAN12       2099-01-12 00:00:00
99JANUARY13   2099-01-13 00:00:00
19990114      1999-01-14 00:00:00
2001-01-15    2001-01-15 00:00:00
2001JAN16     2001-01-16 00:00:00
2001JANUARY17 2001-01-17 00:00:00
20010118      0118-01-20 00:00:00

(注意:正如最后一个示例所示，您正在使用的日期格式是不明确的。您可以交换在函数中解析格式的顺序以获得不同的结果，但是如果有010203，是01-FEB-2003__、02-JAN-2003__、03-FEB-2001还是01-FEB-0003__?)

如果您希望它是DD-MON-YY格式的(为什么是YY而不是YYYY?)那就用：

TO_CHAR( parse_Date_String( value ), 'DD-MON-YY' )