Re: Oracle equivalent of IsDate expression

We had the same problem when porting from SQLServer to Oracle. Oracle was missing several key functions that you'd think they'd have by now.

I would suggest creating a wrapper function for TO_DATE. Let the Exception handler return null if the conversion fails. Be aware though that the TO_DATE function depends on the NLS_DATE_FORMAT value if you do not explicitly specifiy
it. ie. 041310 would be a compatible 'dmy' format ( day before month ) , but not 'mdy'. ( month before day)

In your case, it sounds like you know the format of this date string in advance, so you can be explicit with 'YYMMDD'.

FUNCTION TO_DATE2(vcDate IN VARCHAR2)
RETURN DATE
IS

dtDate DATE;
BEGIN

• you could do substring here on vcDate to hide it from the view code

RETURN( TO_DATE(vcDate, 'YYMMDD' ));

EXCEPTION     WHEN OTHERS         THEN RETURN NULL; END;

-- 


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"Paul Brownjohn" <Paul-no-spam-_at_brownjohn.co.uk> wrote in message
news:417fe65a$1_at_news.vo.lu...


> 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