Issue in a function that converts a string into a valid date

I have been using this function for last few years that converts an input string into a valid date in decided format.

select FN_RETURN_VALID_DATE('20200228005815354241') from dual;

The above query will return, 28-02-2002 00:58:15

However starting 29/02/2020, if the input string to the function changes to ‘20200229005947354241′, it returns a null! I am not sure if this something related to leap year but not able to figure it out yet.

The function definition is,

CREATE OR REPLACE FUNCTION "FN_RETURN_VALID_DATE" ( v_STR IN VARCHAR2 ) RETURN 
DATE
IS
BEGIN
RETURN TO_DATE
( 
    substr(v_STR, 7, 2) || '-' || 
    substr(v_STR, 5, 2) || '-' ||  
    substr(v_STR, 2, 2) || ' ' ||
    substr(v_STR, 9, 2) || ':' || 
    substr(v_STR, 11, 2) || ':' || 
    substr(v_STR, 13, 2), 'DD-MM-YY HH24:MI:SS') ;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

I have checked that there is no error as when I checked I received this message “ORA-0000: normal, successful completion”. Please let me know what could be the issue.

Regards, S

Answer

It could be simply done in pure SQL:

with data as(
    select '20200229005947354241' input_dt from dual
)
select input_dt, 
       to_char(
           to_date(
               substr(input_dt, 1, 14), 
               'YYYY-MM-DD HH24:MI:SS'), 
          'DD-MM-YYYY HH24:MI:SS') dt 
from data;

INPUT_DT             DT                 
-------------------- -------------------
20200229005947354241 29-02-2020 00:59:47

Another thing in your code which is really dangerous:

EXCEPTION
WHEN OTHERS THEN
RETURN NULL;

It’s a bug waiting for its chance to break the atomicity of a procedural call. You are hiding the error in your code. Please see WHEN OTHERS – A bug