1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| WITH MaTable AS
(
select '01/01/2009' as dt_ent, '02:13' as hr_ent, '02/01/2009' as dt_srt, '15:10' as hr_srt from dual
)
, MaTable2 AS
(
select to_date(dt_ent || hr_ent, 'dd/mm/yyyyhh24:mi') as dt_ent_full,
to_date(dt_srt || hr_srt, 'dd/mm/yyyyhh24:mi') as dt_srt_full
from MaTable
)
select dt_ent_full, dt_srt_full,
trunc( dt_srt_full-dt_ent_full ) as Jour,
trunc( mod( (dt_srt_full-dt_ent_full)*24 , 24 ) ) as Heure,
trunc( mod( (dt_srt_full-dt_ent_full)*24*60 , 60 ) ) as Minutes,
trunc( mod( (dt_srt_full-dt_ent_full)*24*60*60, 60 ) ) as Secondes
from MaTable2;
DT_ENT_FULL DT_SRT_FULL JOUR HEURE MINUTES SECONDES
01/01/2009 02:13:00 02/01/2009 15:10:00 1 12 56 59 |
Partager