How to get lowest and highest date from a table column?

user_12926082 0
Viewed 603

Am trying to get the lowest date and highest date from a table column. Am using below SQL query for that.

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

FYI - Am using this query in PL/SQL. From the above query's output I will be generating date range. We are using oracle 19c.

But problem is these columns MOD_BEGIN, MOD_END have very few invalid values (e.g: 00000001000000) due to this when I execute the above query I get error message saying:

ORA-01843: not a valid month
ORA-02063: preceding line from L_IPSL_PPE_MUC

We are not allowed to cleanup this invalid data.

How to handle this scenario?

oracle19c aggregate-functions datetime oracle sql
2 Answers

If you are running Oracle 12.2 or higher, this might be an handy place to use ON CONVERSION ERROR:

select 
    trunc(min(to_date(mod_begin default null on conversion error, 'yyyymmddhh24miss'))) as min_datum,
    trunc(max(to_date(mod_end   default null on conversion error, 'yyyymmddhh24miss'))) as max_datum 
from v_ipsl_ppe_muc_azeit;

When an invalid date is met, to_date() returns null instead of erroring; then the surrounding aggregate function ignore null values.

user_106767160

Another solution can be using custom conversion function which will return NULL in case of conversion error:

FUNCTION convert_date (p_str IN varchar2, p_format varchar2)

RETURN DATE IS

BEGIN

    RETURN  TO_DATE(p_str , p_format);

EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;

END convert_date ;

And use this function in your code:

select 
    trunc(min(convert_date (mod_begin, 'yyyymmddhh24miss'))) as min_datum,
    trunc(max(convert_date (mod_end, 'yyyymmddhh24miss'))) as max_datum 
from v_ipsl_ppe_muc_azeit;
user_27003440
Related