CODEWITHSUNDEEP
sqloracle-databaseplsql
sachin vaidya
sachin vaidya

Reputation: 65

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

Upvotes: 1

Views: 57

Answers (2)

Anup Thomas
Anup Thomas

Reputation: 198

The issue is with year format, if you are using 2 digit year format "RR" will give you current century. However it is best is to use 4 digit Year format to avoid confusion

CREATE OR REPLACE FUNCTION "FN_CHK_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, 0, 4) || ' ' ||
    substr(v_STR, 9, 2) || ':' || 
    substr(v_STR, 11, 2) || ':' || 
    substr(v_STR, 13, 2), 'DD-MM-YYYY HH24:MI:SS') ;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

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

Upvotes: 1

Related Questions