| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Need function "is_date" for converting varchar2 to date-format
Peter F. wrote:
> 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
You need a PL/SQL function like this one:
create or replace function safe_to_date(p_string varchar2
,p_default_year date := sysdate)
return date AS
--
l_date date := null;
--
BEGIN
--
BEGIN
--
l_date := to_date(substr(p_string,1,10),'DD-MM-YYYY');
--
EXCEPTION WHEN others THEN
--
-- Assume year is missing or invalid
-- Try replacing it with our default year
--
BEGIN
--
l_date :=
to_date(substr(p_string,1,5)||'-'||to_char(p_default_year,'YYYY'),'DD-MM-YYYY');
--
EXCEPTION WHEN others THEN
--
l_date := p_default_year;
--
END;
--
END;
--
return( l_date);
--
END;
/
This takes 2 parameters - A string containing what you think is a date
and a date which is used when your string is day and month only. Dates
in oracle must be complete - '01-04' is not a valid date. This function
gets round this by using the year of the optional second parameter. You
can use this function like any other SQL function. Some examples:
/* Pass in a valid date */
SQL> select safe_to_date('01-04-2001') from dual;
SAFE_TO_D
---------
01-APR-01
/* Pass in day and month. Get 'Year' from system date */
SQL> select safe_to_date('01-04',sysdate) from dual;
SAFE_TO_D
---------
01-APR-04
/* Pass in rubbish. Get system date back */
1* select safe_to_date('jsjdjdjdj',sysdate) from dual
SAFE_TO_D
---------
25-MAY-04
/* By picking a 'special' date as the second parameter you can tell how
much of your original date was valid. In the example below we pick a
yeat far in the future as a default */
SQL> r
1* select
to_char(safe_to_date('12-06-????',to_date('01-JAN-4712')),'DD-MM-YYYY')
from dual
TO_CHAR(SA
----------
12-06-4712
David Rolfe
Orinda Software
Dublin, Ireland
-------------------------------------------------------------------------------
Orinda Software make OrindaBuild, An Oracle/JDBC/Java Code Generator.
www.orindasoft.com
Received on Tue May 25 2004 - 07:50:20 CDT
 |
 |