add_years and years_between

I just wrote those two functions

add_years and years_between

they work similary to add_months and months_between, with the exception of leap years.

there is exactly 1 year between 28-feb-2003 and 28-feb-2004
there is 1.00273224 year (1+1/366) between 28-feb-2003 and 29-feb-2004
there is 0.99726776 year (1-1/366) between 29-feb-2004 and 28-feb-2005
there is exactly 1 year between 29-feb-2004 and 01-mar-2005

ok, here it is:

create or replace function add_years( d1 date, n number) return date is
d2 date;
begin
if ( n=0) then
return d1;
end if;
if ( d1 is null or n is null)
then
return null;
end if;
if ( to_char( d1, ‘MMDD’)=’0229′) then
if ( mod( n,1)=0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’);
elsif ( n>0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)+1,’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’)-
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’));
else
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||’060’||to_char( d1,’HH24MISS’), ‘SYYYYDDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’)-
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)-1,’0000′)||’060’||to_char( d1, ‘HH24MISS’),’SYYYYDDDHH24MISS’));
end if;
else
if ( mod( n,1)=0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’);
elsif ( n>0) then
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)+1,’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’) –
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’));
else
d2:=to_date( to_char( to_char( d1,’SYYYY’)+ trunc( n),’0000′)||to_char( d1,’MMDDHH24MISS’), ‘SYYYYMMDDHH24MISS’) +
mod( n,1)*( to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n),’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’) –
to_date( to_char( to_char( d1, ‘SYYYY’)+trunc( n)-1,’0000′)||to_char( d1, ‘MMDDHH24MISS’),’SYYYYMMDDHH24MISS’));
end if;
end if;
return d2;
end;
/

create or replace function years_between( d1 date, d2 date) return number is
n number;
begin
if ( d1=d2) then
return 0;
end if;
if ( d1 is null or d2 is null) then
return null;
end if;
n:=trunc( ( to_char( d2,’SYYYYMMDDHH24MISS’)-to_char( d1, ‘SYYYYMMDDHH24MISS’))/10000000000);
if ( to_char( d1, ‘MMDD’)=’0229′) then
if ( d1<d2) then
if ( to_char( to_date( ( to_char( d1,’SYYYY’)+n)|| ‘060’,’SYYYYDDD’),’MMDD’)=’0229′)
then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’) +n)||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/366;
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/365;
end if;
else
if ( to_char( to_date( ( to_char( d1,’SYYYY’)+n-1)|| ‘060’,’SYYYYDDD’),’MMDD’)=’0229′)
then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’) +n)||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/366;
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||’060’||to_char( d1,’HH24MISS’),’SYYYYDDDHH24MISS’))/365;
end if;
end if;
else
if ( d1<d2) then
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’))/
( to_date( ( to_char( d1,’SYYYY’)+n+1)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’) –
to_date( ( to_char( d1,’SYYYY’)+n)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’));
else
n := n + ( d2-to_date( ( to_char( d1,’SYYYY’)+n) ||to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’))/
( to_date( ( to_char( d1,’SYYYY’)+n)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’) –
to_date( ( to_char( d1,’SYYYY’)+n-1)|| to_char( d1,’MMDDHH24MISS’),’YYYYMMDDHH24MISS’));
end if;
end if;
return n;
end;
/

the logic is : if you are born 29-february, you will get your birthday the 60th day of the year. Otherwise, you will get your birthday on the same date as when you were born. The rest is to calculate fraction of year, and it “should” work with negatives, too.