Re: Oracle equivalent of IsDate expression

"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

you need to create a function with an exception handler, something like:

create or replace function isdate( ip_date in varchar2 ) return date
is
begin

     return to_date(ip_date,'yyyymmdd'); exception
when others
then return null;
end isdate; Received on Wed Oct 27 2004 - 14:09:07 CDT