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

From: Peter F. <peter.finke_at_bva.bund.de>
Date: 25 May 2004 05:16:10 -0700
Message-ID: <bb1d9c68.0405250416.2abb4ffe@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 Received on Tue May 25 2004 - 07:16:10 CDT