Re: Need function "is_date" for converting varchar2 to date-format

I *think* I would write is_date as

create or replace function is_date(dt IN varchar2,fmt IN varchar2) return number
is
d date;
begin
d := to_date(dt,fmt);
return 1;
exception

    when others

        return 0;
end;
/

where dt is the string and fmt a valid date format.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
http://www.niall.litchfield.dial.pipex.com/
"Peter F." <peter.finke_at_bva.bund.de> wrote in message
news:bb1d9c68.0405250416.2abb4ffe_at_posting.google.com...


> Hello together,

>

> in a view i need a function to convert varchar2 to date-format.

> I need the varchar2-format, because the birthday is not always

> completely known. If its not the right format it must scip. Ive tried

> it like this, but its failed :

>

> VIEW VIEW_MA_4010_2 (LG_ID, DAT ) AS

> SELECT tn_LG_ID, to_Date(substr(tn_geburtsdatum,1,10),'DD-MM-YYYY')

> FROM TB_TEILNEHMER

> WHERE (tn_geburtsdatum is not null or tn_geburtsdatum <> '')

> and length(tn_geburtsdatum) = 10

> and substr(tn_geburtsdatum,3,1)='.'

> and substr(tn_geburtsdatum,6,1)='.'

> and to_number(substr(tn_geburtsdatum,1,2))> 0

> and to_number(substr(tn_geburtsdatum,1,2))< 32

> and to_number(substr(tn_geburtsdatum,4,2))> 0

> and to_number(substr(tn_geburtsdatum,4,2))< 13

> and to_number(substr(tn_geburtsdatum,7,4)) > 999

> and to_number(substr(tn_geburtsdatum,7,4)) < 9999

>

> When it runs throught, depending on stored data, the use of the view

> as sub-view (searching min(dat)) ends with error:

>

> ora-01841 (year not between -4713 and +9999)

> ora-01839 (wrong month) etc.:

>

> VIEW VIEW_MA_4010_11 (LG_ID, DATUM1 ) AS

> SELECT LG_ID, min(dat)

> FROM VIEW_MA_4010_2

> GROUP BY LG_ID