to_date - Oracle: convierte muchos formatos de fecha en una sola fecha formateada

sumar dias a una fecha oracle

Question

Quiero traer una cadena que contiene una fecha a una fecha de formato único. EX:

• 13-06-2012 a 13-JUN-12
• 13/06/2012 al 13-JUN-12
• 13-JUN-2012 al 13-JUN-12
• 13 / jun-2012 a 13-JUN-12
• ...

Traté de borrar todos los caracteres especiales y después de eso uso una función para transformar esa cadena en un único formato de fecha. Mi función devuelve más excepciones, no sé por qué ...

La función:

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;

Uso

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

Si tiene una buena idea de todos los posibles formatos de fecha, podría ser más fácil usar la fuerza bruta:

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

Tenga en cuenta que las versiones modernas de Oracle son bastante indulgentes con la conversión de fechas. Esta función maneja las fechas en formatos que no están en la lista, con algunas consecuencias interesantes:

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> 

Lo que demuestra los límites de la limpieza automatizada de datos frente a las reglas de integridad de datos laxas. El salario del pecado es información corrupta.

@AlexPoole plantea la cuestión del uso del formato ''RR'' . Este elemento de la máscara de fecha se introdujo como un kludge de Y2K. Es bastante deprimente que sigamos discutiéndolo casi dos décadas en el nuevo Milenio.

De todos modos, el problema es esto. Si lanzamos esta cadena ''161225'' a una fecha, ¿qué siglo tiene? Bueno, ''yymmdd'' dará el 2016-12-15 . Muy bien, pero ¿qué pasa con ''991225'' ? ¿Qué tan probable es que la fecha que realmente queremos sea 2099-12-15 ? Aquí es donde el formato ''RR'' entra en juego. Básicamente es el valor por defecto del siglo: los números 00-49 por defecto a 20, 50-99 por defecto a 19. Esta ventana fue determinada por el problema del año 2000: en 2000 era más probable que el ''98 refiriera al pasado reciente que al futuro cercano, y lógica similar aplicada a ''02 . De ahí el punto medio de 1950. Tenga en cuenta que este es un punto fijo, no una ventana deslizante. A medida que nos alejamos del año 2000, se vuelve menos útil ese punto de pivote. Descubra más .

De todos modos, el punto clave es que ''RRRR'' no funciona bien con otros formatos de fecha: to_date(''501212'', ''rrrrmmdd'') hurls ora-01843: no es un mes válido . So, use . So, use ''RR'' and test for it before using pruébelo and test for it before using ''YYYY''. Así que mi función revisada (con algunos arreglos) se ve así:

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

El punto clave sigue siendo: existe un límite en lo inteligente que podemos hacer esta función cuando se trata de interpretar las fechas, así que asegúrese de liderar con el mejor ajuste. Si crees que la mayoría de las cadenas de fechas coinciden día-mes-año, ponlo primero; aún obtendrá algunos lanzamientos incorrectos pero menos que si lidera con año-mes-día.

Answer 2

Las reglas de conversión de cadenas a la fecha permiten reglas de formato adicionales (sin aplicar ningún otro modificador). (También vea esta pregunta ) Entonces:

• MM también coincide con MON y MONTH ;
• MON también coincide con MONTH (y viceversa);
• YY también coincide con YYYY ;
• RR también coincide con RRRR ; y
• La puntuación es opcional.

Lo que significa que puedes hacer:

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

Consulta :

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;

Salida :

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

(Nota: los formatos de fecha que está utilizando son ambiguos, como lo demuestra el último ejemplo. Puede cambiar el orden en que se 010203 los formatos en la función para obtener resultados diferentes, pero si tiene 010203 es 01-FEB-2003 , 02-JAN-2003 , 03-FEB-2001 o incluso 01-FEB-0003 ?)

Si lo quiere en el formato DD-MON-YY (pero ¿por qué YY y no YYYY ?), Simplemente use:

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