Create SQL Query to bring results from a field which does not follow a required format

Small Update:

Now run this query to find out which are all invalid records:

select * from urtable
where isdate(datecol, dateformat) = 1

note the format above also restricts the date range to be within 20th or 21st century

merely checking for valid dates alone is not enough, they have to be valid and within the proper range

rrjegan17,
I don't know about the isdate function in Oracle,  I don't see it in the 11g SQL Reference and it's not recognized in my 11.1.0.7 db.

Is it documented and supported?  If so, where?  I'd like to read more about the api for it.  
Does it come with a certain patch, if so, which one?  It would be a nice addition to the sql language.

sdstuber,
   I am using Oracle 10g and it don't have it.
When I searched for this asker, I found out that this is available in 11g.

Please find the reference below for your information

http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_functions_1106.htm

Hope I clarified you out.

ok, you have to have the separately licensed OLAP stuff.  Which I do not in my db.

Thanks!

it doesn't appear that isdate has the flexibility the asker is looking for
that is, you can't use it to specify a particular format and determine if the string follows that format
and, if you don't have OLAP you can't use it anyway.

but, thanks for the info anyway, the OLAP stuff is interesting even if not applicable to this question specifically

one additional note

using regular expressions like above does allow you to check the format
but, even though it will enforce the centuries and look "mostly" like a date
it's still completely reliable.

'02/31/2009'  will pass the regular expression check but the string does not represent a valid date.

sdstuber,
   But the equivalent one given in my comment no 24016216 handles it out right.

yeah, same as my check_date_str except I include the century check
same idea and easy to include in yours too

That last note about regulare expressions wasn't directed to you, it was to the asker.  I wanted to make sure that even though I showed how to use the expression posted in the original question, it still wasn't a reliable way of finding good date strings

Though I am gonna accept both the solutions, since both of them gave the same result with the use of the additional function, but for some reason, none of them were able to show only the results which do not follow the format, it showed me all the results where duedate had any date. Luckily checking the results I found out that results not in proper format are only in tens and hence was able to rectify them manually.

" none of them were able to show only the results which do not follow the format, "

how were you using the function?

please provide an example that did not work

Hi sdstuber,

I am using toad, and ran the function through schema browser, then ran the query in normal editor. Even this query worked, since it was 10g but again with same result, i.e gave all the results which had any date in duedate column.

"select * from invoice where not regexp_like(due_date,'(0?[1-9]|1[012])/(0?[1-9]|[12][0-9]|3[01])/(19|20)\d\d')"

My assumption is now that, the format is basically for the application side where it checks for the input from user whenever the field is a date, for some reason, for this particular field 'duedate', this format criteria was not put in on the application side and Oracle/sql cannot differentiate based on this format?

The faulty dates were like this '07/31/0207' or '11/21/0007'.