Re: date/time problem

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 23 Oct 1998 12:11:35 +0200
Message-ID: <70pksg$n4b$1@hermes.is.co.za>

andy sykes wrote in message <36304F6A.3AC8B4FF_at_holland.sun.com>...

>I want to compare two dates and display the difference in Hours,Mins &
>seconds. Has anyone had to do this or can anyone suggest a solution ?

The only "proper" way I can think of is the long way - run a TO_CHAR to convert the date to a string, TO_NUMBER to convert that into seconds and subtract from the other date that goes thru the same conversion.

Ignoring the month and year for the moment (which I'm allowed to do as I'm part of the anti 2YK group ;-) :

SELECT
  TO_NUMBER     TO_CHAR( sysdate, 'dd' )

         ) * 24 * 60 * 60 DAYS,
  TO_NUMBER     TO_CHAR( sysdate, 'hh' )

         ) * 60 * 60 HOURS,
  TO_NUMBER     TO_CHAR( sysdate, 'mi' )

         ) * 60 MIN,
  TO_NUMBER     TO_CHAR( sysdate, 'ss' )

         )                 SEC

FROM dual

This will return the day, hour, minutes as seconds. Simply change this SQL to sum the parts with the seconds and subtract from the other date.

Also maybe a better idea to implement this via a user function. Less change for errors and less network traffic to send.

regards,
Billy Received on Fri Oct 23 1998 - 05:11:35 CDT