Re: Subtracting Dates

> I need to show the difference between dates in format: hh24:mi:ss.
>

Like this:

create table datesub(time1 date, time2 date);

insert into datesub
values (to_date('20030703 123456','yyyymmdd hh24miss'),to_date('20030707 082334','yyyymmdd hh24miss'));

insert into datesub
values (to_date('20030703 123456','yyyymmdd hh24miss'),to_date('20030701 082334','yyyymmdd hh24miss'));

select floor(time2-time1) || ' day(s) ' || to_char(to_date('20000101','yyyymmdd') + (time2-time1),'hh24:mi:ss') as diff from datesub
where time2 >= time1
union
select '-' || floor(time1-time2) || ' day(s) ' || to_char(to_date('20000101','yyyymmdd') + (time1-time2),'hh24:mi:ss') as diff from datesub
where time1 >= time2;

The "to_date('0','ss')" is added to convert the difference (which is a number) to a date. The date doesn't matter but the time part is needed. Received on Tue Jul 29 2003 - 11:05:51 CDT