/* @%ORACLE_HOME%:Ej_Fechas */
set echo ON

column f_Com    heading "fecha Comienzo" format a22;
column f_Fin    heading "fecha Final"    format a22;
column f_Dif    heading "dif.(dias)"     format 9999.99999;
column dias     heading "dias"           format 9999;
column horas    heading "h."             format 99;
column minutos  heading "m."             format 99;
column segundos heading "s."             format 99;

drop table temp;
create table temp(fechaCom date,
                  fechaFin date,
                  numRef   number(4),
                  difTim   number);
insert into temp
  values ('12-jan-94', '13-jan-94', 1, NULL);
select * from temp;

insert into temp
  values (to_date('12-5-93','DD-MM-YY'),
          to_date('16-May-93','DD-Mon-YY'),
          2,
          NULL);
select * from temp;

insert into temp
  values (to_date('12-5-93 14:23:5','DD-MM-YY HH24:MI:SS'),
          to_date('14-5-93  4:25:32 PM','DD-MM-YY HH:MI:SS AM'),
          3,
          NULL);
select * from temp;

drop view demo;
create view demo (f_Com, f_Fin, f_Dif, dias, horas, minutos, segundos) as
 select to_char(fechaCom,'HH24:MI:SS DD Mon YYYY'),
        to_char(fechaFin,'HH24:MI:SS DD Mon YYYY'),
        difTim,
        trunc(difTim,0),
        trunc((difTim-trunc(difTim,0))*24,0),
        trunc((difTim*24-trunc(difTim*24,0))*60,0),
        round((difTim*24*60-trunc(difTim*24*60,0))*60,0)
 from temp;

update temp set difTim = fechaFin-fechaCom;

select * from demo;