Re: date/time problem

From: Jurij Modic <jmodic_at_src.si>
Date: Fri, 23 Oct 1998 12:00:02 GMT
Message-ID: <36306e74.19746901@news.siol.net>

On Fri, 23 Oct 1998 11:42:02 +0200, andy sykes <andrew.sykes_at_holland.sun.com> wrote:

>Hi All,
>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 ?
>I have tried the months_between function but it doesn't appear to work
>when the dates are the same e.g
>
>SELECT MONTHS_BETWEEN(TO_DATE('10-23-1998 09:30:00', 'MM-DD-YYYY
>HH:MI:SS'),TO_DATE('10-23-1998 11:35:00', 'MM-DD-YYYY HH:MI:SS'))
>"Months" FROM DUAL
>
>returns ;
>
> Months
>----------
> 0
>Assuming that this was to give me a decimal value corresponding to the
>time,(the help says that is should), how would I convert that into
>meaningfull Hrs,Min & Secs values ?
>I would really appreciate some suggestions.

You can subtract dates, you don't need to use MONTHS_BETWEEN. Here is a simple function that displays days and time between two dates:

SQL> CREATE OR REPLACE FUNCTION date_difference (date1 DATE, date2 DATE)
  2 RETURN VARCHAR2 IS
  3 diff NUMBER := ABS(date2 - date1);   4 signs VARCHAR2(1);

  5    days  NUMBER;
  6    hours NUMBER;
  7    mins  NUMBER;
  8    secs  NUMBER;

  9 BEGIN
 10 IF date2 < date1 THEN
 11 signs := '-';
 12 END IF;
 13 days := TRUNC(diff);
 14 diff := diff - days;
 15 hours := TRUNC(diff*24);

 16    diff := diff - hours/24;
 17    mins := TRUNC(diff*1440);   -- 24*60
 18    diff := diff - mins/1440;
 19    secs := TRUNC(diff*86400);  -- 24*60*60
 20    RETURN (signs || days || ' days, ' || hours || ' hours, '||
 21            mins ||' minutes, ' || secs || ' seconds');

 22 END;
 23 /

Function created.

SQL> show errors
No errors.
SQL>
SQL> SELECT date_difference(TO_DATE('10-23-1998 09:30:00', 'MM-DD-YYYY HH:MI:SS')
  2 ,TO_DATE('10-23-1998 11:35:00', 'MM-DD-YYYY HH:MI:SS'))   3 "Difference" FROM DUAL;

Difference

---

0 days, 2 hours, 5 minutes, 0 seconds

>Regards
>Andy

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Oct 23 1998 - 07:00:02 CDT