| my_to_date SQLLDR [message #128611] |
Tue, 19 July 2005 14:45  |
rbackmann
Messages: 2
Registered: July 2005
|
Junior Member |
|
|
I am having trouble getting the following to compile.
I have I am running SQLLDR and the source systems could have a variety of date formats with invalid dates
create or replace function my_to_date
(p_string in varchar2)
return date
as
len_str number;
instr1 varchar2(2);
instr2 varchar2(2);
instr3 varchar2(2);
begin
len_str := length(p_string);
instr1 := substr(p_string,3,1) || substr(p_string,6,1);
instr2 := substr(p_string,5,1) || substr(p_string,8,1);
instr3 := substr(p_string,3,1) || substr(p_string,7,1);
case
when len_str = 10 then
case when instr1 = '//' then
return to_date (p_string, 'mm/dd/yyyy');
-- case when instr1 = '--' then
-- return to_date (p_string, 'mm-dd-yyyy');
-- case when instr2 = '--' then
-- return to_date (p_string, 'yyyy-mm-dd');
end case;
-- when len_str = 11 then
-- case when instr3 = '--' then
-- return to_date (p_string, 'dd-mon-yyyy');
-- end case;
-- when len_str = 8 then
-- case when instr1 = '//' then
-- return to_date (p_string, 'mm/dd/yy');
-- case when instr1 = '--' then
-- return to_date (p_string, 'mm-dd-yy');
-- end case;
-- when len_str = 9 then
-- case when instr3 = '--' then
-- return to_date (p_string, 'dd-mon-yy');
-- end case;
end case;
exception
when others then return null;
end;
Any help greatly appreciated.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
