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