Re: Difference between DATE fields.

 Gilberto Casiraghi wrote in article <01bcb94b$7f4a6900$6500a8c0_at_edipi9b>...

>I would like to calculate the difference between DATE fields.

Try this -

create or replace function date_difference (pi_first_date date, pi_second_date date) return varchar2
is

--
l_secs_in_an_hr  number := 60*60;
l_secs_in_a_day  number := 60*60*24;
l_secs_in        number;
l_secs_remaining number;
l_secs_in_a_yr   number := l_secs_in_a_day*365.25;
l_secs_in_a_mnth number := l_secs_in_a_yr/12;
l_yrs            number := 0;
l_mnths          number := 0;
l_days           number := 0;
l_hrs            number := 0;
l_mins           number := 0;
l_secs           number := 0;
l_string_out     varchar2(20);
--
begin
   --
   l_secs_in        := (pi_second_date - pi_first_date) * l_secs_in_a_day;
   --
   l_yrs            := trunc(l_secs_in/l_secs_in_a_yr);
   l_secs_remaining := mod(l_secs_in,l_secs_in_a_yr);
   l_mnths          := trunc(l_secs_remaining/l_secs_in_a_mnth);
   l_secs_remaining := mod(l_secs_remaining,l_secs_in_a_mnth);
   l_days           := trunc(l_secs_remaining/l_secs_in_a_day);
   l_secs_remaining := mod(l_secs_remaining,l_secs_in_a_day);
   l_hrs            := trunc(l_secs_remaining/l_secs_in_an_hr);
   l_secs_remaining := mod(l_secs_remaining,l_secs_in_an_hr);
   l_mins           := trunc(l_secs_remaining,60);
   l_secs           := mod(l_secs_remaining,60);
   --
   l_string_out     := lpad(to_char(l_yrs),4,'0');
   l_string_out     := lpad(to_char(l_mnths),2,'0')||'/'||l_string_out;
   l_string_out     := lpad(to_char(l_days),2,'0')||'/'||l_string_out;
   l_string_out     := l_string_out||' '||lpad(to_char(l_hrs),2,0);
   l_string_out     := l_string_out||':'||lpad(to_char(l_mins),2,0);
   l_string_out     := l_string_out||':'||lpad(to_char(l_secs),2,0);

   --
   return(l_string_out);
   --
end;