Oracle equivalent of IsDate expression

I need to examine the contents of part of a char field and and determine if it can be converted into a valid date.

With SQL Server, I would use IsDate to determine whether it was vadid for date conversion but I cannot find an equivalent in Oracle (which is a bit embarrasing to say the least as I have been using Oracle 7.x, 8i and 9i for 10 years or more and I can't believe that I haven't come across this problem before).

If TRAD_DESC contains a space delimited string something like:

'041105 tt889652 44555223653562 '

then the code I tried works, however the column TRAD_DESC can also contain something really unhelpful like:

'Contribution $173265.32 gg7878 '

in which case of course it doesn't work. The code I tried was something like:

SELECT
TO_DATE(SUBSTR(ga.TRAD_DESC, 1, 6), 'YYMMDD') as trad_dt FROM global_activity ga

What I need to do is to extract the date if it is there and it is valid (i.e. it is in the format 'YYMMDD') or return NULL

if it is not valid.

BTW this is for a view so I can't do anything too fancy in it!

Any suggestions?

TIA Paul BJ Received on Wed Oct 27 2004 - 13:21:31 CDT