Re: Subtracting Dates

From: Joe Zafian <joe.zafian_at_espn.com>
Date: 31 Jul 2003 11:46:02 -0700
Message-ID: <4e8c1fda.0307311046.32cf13bd@posting.google.com>

"programmer" <int.consultNOCAPITALS_at_macmail.com> wrote in message news:<bg66g6$ki0$1_at_pheidippides.axion.bt.co.uk>...
> > 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.

another approach is

select to_char (to_date (lpad (to_char (trunc (86400 * (date2 - date1))),

                              5, '0'), 'SSSSS'), 'HH24:MI:SS') 

  from my_tab Received on Thu Jul 31 2003 - 13:46:02 CDT