Function to Find Valid Invalid Date Format Oracle

Some times we need to check data inside the column if it is in proper shape or proper format. Like some of database saving date data in side of varchar field. which is wrong, if could have invalid date format or can have bad data in terms of date, month, year.

Here i am going to provide you the example of function to check date is properly saved in varchar column, regardless of any date format. Function will check for all combination of date type format and will return valid or invalid i.e. atleast one format is correctly saved in column.

create function datetype1(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MON-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype2(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MON-YY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype3(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD-MM-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype4(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'MM-DD-YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype5(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'YYYY-MM-DD') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype6(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'DD/MM/YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype7(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'MM/DD/YYYY') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/

create function datetype8(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'YYYY/MM/DD') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;
/



CREATE OR REPLACE Function DateFieldValidInvalid
   ( field_v in varchar2)
   RETURN varchar2
IS
   cnumber varchar2(2000);
v_query1 varchar2(4000);

BEGIN
v_query1 := 'select a from (select datetype1(''' || field_v||''') a from dual union ' ||
'select datetype2(''' || field_v||''') from dual union ' ||
'select datetype3(''' || field_v||''') from dual union ' ||
'select datetype4(''' || field_v||''') from dual union ' ||
'select datetype5(''' || field_v||''') from dual union ' ||
'select datetype6(''' || field_v||''') from dual union ' ||
'select datetype7(''' || field_v||''') from dual union ' ||
'select datetype8(''' || field_v||''') from dual) where a = ''Valid''' ;
 
execute immediate v_query1 into cnumber;

RETURN cnumber;
END;


select DateFieldValidInvalid(created_ON) from t_contact ;