REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor "Dasages, LLC" makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM
REM Please contact the author via email (dave@dasages.com) when
REM you have comments, suggestions, and/or difficulties with this
REM package or its functions.
REM
REM [Please keep the above disclaimer and the embedded electronic
REM documentation with this script.]
REM **************************************************************
create or replace function date_ck (dt_in in varchar2) return varchar2 is
delim1_pos number;
delim2_pos number;
delim3_pos number;
mon_num number;
day_num number;
year_num number;
dt_hold varchar2(50);
dt_temp varchar2(50);
dayyear_hold varchar2(50);
month_days varchar2(24) := '312831303130313130313031';
function dh_char (str in varchar2, len in number) return varchar2 is
hold_str varchar2(50) := '00000000000000000000000000'||str;
hold_len number;
begin
hold_len := length(hold_str);
return substr(hold_str,hold_len-(len-1),len);
end;
function ambiguous_dt return varchar2 is
str_hold varchar2(100);
begin
str_hold := dt_hold||' Error: date is ambiguous.('||
to_char(to_date('0'||dt_hold,'mmddrr'),'dd-MON-yyyy')||','||
to_char(to_date(substr(dt_hold,1,2)||'0'||substr(dt_hold,3),'mmddrr'),
'dd-MON-yyyy')||')';
return str_hold;
end;
begin
delim1_pos := 0;
delim2_pos := 0;
dt_hold := translate(dt_in,'/. ','---');
delim1_pos := instr(dt_hold,'-');
dt_temp := translate(dt_hold,'0123456789','----------');
if length(rtrim(dt_temp,'-')) > 0 then
return dt_in||' Error: 0-9,/,-,<sp> chrs. only.';
end if;
if delim1_pos > 0 then
delim2_pos := instr(dt_hold,'-',delim1_pos+1);
if delim2_pos = 0 then
return dt_in||' Error: delimeters must be in pairs.';
else
delim3_pos := instr(dt_hold,'-',delim2_pos+1);
if delim3_pos > 0 then
return dt_in||' Error: only two delimeters allowed.';
end if;
end if;
mon_num := substr(dt_hold,1,delim1_pos-1);
day_num := substr(dt_hold,delim1_pos+1,delim2_pos-delim1_pos-1);
year_num := substr(dt_hold,delim2_pos+1);
if year_num between 0 and 49 then
year_num := year_num + 2000;
elsif year_num between 50 and 99 then
year_num := year_num + 1900;
elsif year_num between 1900 and to_char(sysdate,'yyyy') then
null;
else
return dt_in||' Error: year must be 1900 - '||
to_char(sysdate,'yyyy')||'.';
end if;
if mon_num not between 1 and 12 then
return dt_in||' Error: bad month number.';
end if;
if day_num not between 1 and substr(month_days,((mon_num-1)*2)+1,2) then
if mon_num = 2 and mod(year_num,4)=0 and day_num = 29 then
null;
else
return dt_in||' Error: bad day number.';
end if;
end if;
begin
return to_char(to_date(dh_char(mon_num,2)||dh_char(day_num,2)||
dh_char(year_num,4),'mmddyyyy'),'dd-MON-YYYY');
exception
when others then
return dt_in||': '||sqlerrm;
end;
else
-- Month validation
if substr(dt_hold,1,1) = 0 then
if substr(dt_hold,2,1) = 0 then
return dt_in||' Error: bad month number.';
end if;
elsif substr(dt_hold,1,1) = 1 then
if length(dt_hold) = 4 then
null;
dt_hold := '0'||dt_hold;
elsif length(dt_hold) = 5 then
if substr(dt_hold,2,1) between 0 and 2 then
return ambiguous_dt;
elsif substr(dt_hold,2,2) > 31 then
return dt_in||' Error: bad day number.';
end if;
dt_hold := '0'||dt_hold;
elsif length(dt_hold) = 6 then
if substr(dt_hold,2,1) between 1 and 2 and
substr(dt_hold,3,4) between 1990 and
to_char(sysdate,'yyyy') then
return ambiguous_dt;
end if;
end if;
else
dt_hold := '0'||dt_hold;
end if;
mon_num := substr(dt_hold,1,2);
-- Day/Year validation
DayYear_hold := substr(dt_hold,3);
if length(DayYear_hold)=3 then
day_num := substr(DayYear_hold,1);
year_num := Substr(DayYear_hold,2);
elsif length(DayYear_hold)=4 then
day_num := substr(DayYear_hold,1,2);
year_num := substr(DayYear_hold,3);
elsif length(DayYear_hold)=5 then
day_num := substr(DayYear_hold,1,1);
year_num := substr(DayYear_hold,2);
elsif length(DayYear_hold)=6 then
day_num := substr(DayYear_hold,1,2);
year_num := substr(DayYear_hold,2);
end if;
if year_num between 0 and 49 then
year_num := year_num + 2000;
elsif year_num between 50 and 99 then
year_num := year_num + 1900;
end if;
begin
return to_char(to_date(dh_char(mon_num,2)||dh_char(day_num,2)||
dh_char(year_num,4),'mmddyyyy'),'dd-MON-YYYY');
exception
when others then
return dt_in||': '||sqlerrm;
end;
end if;
end;
/
=========================================
Sample test values:
select * from dates;
01011999
01/01/1999
01-01-1999
1/1/1999
112999
010199
01/01/99
01/01/00
11999
1/1/00
12399
01011999 01211999
02/29/2006
02/29/01
13/01/01
12/0101
02/29/00
28-JUL-2003
=========================================
Sample function results:
select date_ck(date_in) from dates;
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
29-NOV-1999
01-JAN-1999
01-JAN-1999
01-JAN-2000
11999 Error: date is ambiguous.(19-JAN-1999,09-NOV-1999)
01-JAN-2000
12399 Error: date is ambiguous.(23-JAN-1999,03-DEC-1999)
01011999 01211999 Error: delimeters must be in pairs.
02/29/2006 Error: year must be 1900 - 2003.
02/29/01 Error: bad day number.
13/01/01 Error: bad month number.
12/0101 Error: delimeters must be in pairs.
29-FEB-2000
28-JUL-2003 Error: 0-9,/,-,<sp> chrs. only.
=========================================[code]
Let me know how closely it comes to your needs. I'm happy to modify it for you if you itemise the functional changes you would need.
Cheers,
Dave
Sandy, Utah, USA @ 18:38 GMT, 11:38 Mountain Time
= True