Valid Date Checks in Oracle

Question

I have a date value (either valid date or invalid date) store in varchar format. Is it possible to check the date is valid or not in sql query.

Answer

Yes, if you know the format and with little plsql.

Let's say you have the date in format 'yyyy-mon-dd hh24:mi:ss'.

create function test_date(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'yyyy-mon-dd hh24:mi:ss') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;

Now you can:

select your_date_col, test_date(your_date_col)
from your_table;