There are two ways to do this that I know of. One is to write a UDF. There is currently a UDF available with the Oracle UDF's called "toDate" that converts a character column to a date. It produces an error if the date cannot convert. However, you can easily modify it to return a NULL if the column does not contain a valid date which is what I did with it.
Secondly, you could join to the SYS_CALENDAR table as follows:
sel CALENDAR_DATE FROM your_table yt, LEFT OUTER JOIN SYS_CALENDAR.CALENDAR CAL ON (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = yt.mydate;
If the date is valid, you will get the result in a DATE data type. If the date is invalid, you will get a NULL back.
The column that i have is of type varchar(20) and it have value as "ABCDEFGH". If I ran the above query it returns me all the rows from the table. Say Field ABC of type varchar(20) is having values like 2006/01/01 2006_01_01 abcdehgjih 2001-01-01 01-02-2006 and so on.
I want to find the invalid date not the one that is valid. So my SQL should return only abcdehgjih as invalid date.
sel yt.mydate FROM your_table yt, LEFT OUTER JOIN SYS_CALENDAR.CALENDAR CAL ON (CALENDAR_DATE (FORMAT 'YYYY-MM-DD') (CHAR(10))) = yt.mydate WHERE CALENDAR_DATE IS NULL;
Note that this is only considering the date to be valid if it's in the YYYY-MM-DD format. In order to check the other formats, you would have to do additional left outer joins to the date table with the various formats.