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

"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
Okay, what you do is this. Create a function that when an error arises it returns the date you want. For example,

convert_date( date_text in varchar2,err_date in date default to_date('01/01/1900')) return date;

Then in the body do your manipultation as you have above and convert it to a date and return that date. Put an exception handler in the function (so when it is NOT a valid date) and return the err_date (or maninpulate your input and return a valid date in the error routine.) Then your view will work and you won't have to use to_date in the sql or view DDL. Jim Received on Tue May 25 2004 - 07:26:36 CDT