Как получить самую минимальную и максимальную дату из столбца таблицы содержашего недействительные значения?

Question

Пытаюсь получить самую минимальную и самую максимальную дату из столбца таблицы. Использую следующий запрос в PL/SQL, из результата которого генерируется диапазон дат:

select 
    MIN(trunc(TO_DATE(MOD_BEGIN, 'YYYYMMDDHH24MISS'))) AS MIN_DATE, 
    MAX(trunc(TO_DATE(MOD_END, 'YYYYMMDDHH24MISS'))) AS MAX_DATE
from V_IPSL_PPE_MUC_AZEIT;

Но проблема в том, что столбцы MOD_BEGIN, MOD_END могут содержать недействительные значения (например: 0000000100000000), из-за этого при выполнении запроса будет ошибка:

ORA-01843: not a valid month

Нельзя изменять или удалять эти недействительные данные в таблице.

Как быть тогда в этом случае?

_{Свободный перевод вопроса How to get lowest and highest date from a table column in Oracle от участника @surajs89}

Answer 1

Если версися 12.2 или выше, это весьма подходящий случай для ON CONVERSION ERROR:

with t (mod_begin, mod_end) as (
    select '20200101200000','00000001000000' from dual union all
    select '20200102200000','20200103200000' from dual)
select 
    trunc (min (to_date (mod_begin default null on conversion error, 'yyyymmddhh24miss'))) as mindate,
    trunc (max (to_date (mod_end   default null on conversion error, 'yyyymmddhh24miss'))) as maxdate 
from t
/

MINDATE             MAXDATE            
------------------- -------------------
2020-01-01 00:00:00 2020-01-03 00:00:00

Для недействительных значений to_date() вернёт null вместо ошибки, а агрегирующие функции игнорируют null значения.

Для более ранних версий будет полезна следующая функция вместо прямого вызова to_date():

create or replace function convertdate (str varchar2, fmt varchar2) return date is
begin
    return to_date (str, fmt);
exception when others then 
    return null;
end convertdate;
/

_{Свободный перевод ответа от участника @GMB и ответа от участника @leftjoin}

БЛОГ НА HUSL

Как получить самую минимальную и максимальную дату из столбца таблицы содержашего недействительные значения?

Ответы (1 шт):