Date field checking with plsql

with t as (select '01/02/2010' as dt from dual union all
select '04/03/2010' from dual union all
select '05/30/2010' from dual union all
select '30/01/2010' from dual)-- end of test data
select dt
,case when regexp_like(dt,'^[0-3][0-9]\/[0-1][0-9]/(19|20)[0-9]{2}$')
then to_date(dt, 'DD/MM/YYYY')
else null
end as dt_date
,case when not regexp_like(dt,'^[0-3][0-9]\/[0-1][0-9]/(19|20)[0-9]{2}$')
then 'Invalid Format'
else 'Ok'
end as dt_status
from t


2)


create or replace function test_date(dt IN VARCHAR2) return varchar2 is
v_dt DATE;
begin
v_dt := to_date(dt,'DD/MM/YYYY');
return 'Ok';
exception
when others then
return 'Error';
end;

/

Function created.


with t as (select '01/02/2010' as dt from dual union all
select '04/03/2010' from dual union all
select '05/30/2010' from dual union all
select '30/01/2010' from dual) -- end of test data
select dt, test_date(dt) as status
from t
/


3)


CREATE OR REPLACE FUNCTION IS_DATE(F_DATE VARCHAR2, F_FORMAT VARCHAR2 DEFAULT 'DD-MON-YYYY')
RETURN VARCHAR2
IS
V_DATE DATE;
BEGIN
V_DATE := TO_DATE(F_DATE, F_FORMAT);
RETURN V_DATE;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN NULL;
END;

SELECT ('01-JAN-2010','DD-MON-YYYY') DATE_CHECK FROM DUAL;