求两个日期之间的差，年月日，时分秒

--desc:calculate the number of days,hours,minutes between two dates.

create or replace procedure date_test(start_date date, end_date date) is
  DateLow   date;
  DateHigh  date;
  day1      date;
  day2      date;
  Fraction1 number;
  Fraction2 number;
  days      number;
  HHDec     number; -- Hours (decimal)
  HH        number; -- Hours (integer)
  MMDec     number; -- Minutes (decimal)
  MM        number; -- Minutes (integer)
  SSDec     number; -- Seconds (deminal)
  SS        number; -- Seconds (integrer)
begin
  -- Put the dates in order (important when dealing with the fractions
  -- of days left over from the subtraction - see later).
  DateLow  := least(start_date, end_date);
  DateHigh := greatest(start_date, end_date);
  -- Find the midnight preceding start_date
  Day1 := Trunc(DateLow);
  -- Find the midnight preceding end_date
  Day2 := Trunc(DateHigh);
  -- Calculate the days between the two dates (subtracting two DATEs gives a NUMBER)
  -- A function to calculate the number of days between the two dates would return
  -- this value.
  Days := Day2 - Day1 - 1;
  -- Now, what about the fractions of days left?
  Fraction1 := start_date - Day1;
  Fraction2 := end_date - Day2;
  -- Calculate the total HH (and fractions of HH) from the
  -- fractions of the days left over
  HHDec := ((1 - Fraction1) + Fraction2) * 24;
  -- This could be >1 day
  if HHDec >= 24 then
    HHDec := HHDec - 24;
    Days  := Days + 1;
  end if;
  HH := floor(HHDec);
  -- Here, you have the correct number of days and HH between the two dates.
  -- Now obtain the MM from the fractions of HH.
  MMDec := (HHDec - HH) * 60;
  MM    := floor(MMDec);
  -- And the SS...
  SSDec := (MMDec - MM) * 60;
  SS    := floor(SSDec);
  -- You could carry on from here to get the fractions of a second......
  --
  dbms_output.put_line(to_char(days) || ' Days, ' || to_char(HH) ||
                       ' Hours, ' || to_char(MM) || ' Minutes, ' ||
                       to_char(SS) || ' Seconds. ');
end;