This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 7, 2013 9:45 PM by 884519 RSS

Difference between dates in hours

884519 Newbie
Currently Being Moderated
Hi All,

I need to display difference between two date fields and the resule should display like XX Hours, XX minutes.

eg: select ((completion_date - start_date)*24*60) AS run_time from xx_dba_programs;

Expected O/P : 3 Hours, 25 Minutes.



Database is Oracle 11g(Rel.2)

Thanks,
gvk.
  • 1. Re: Difference between dates in hours
    Oracle Maniac Explorer
    Currently Being Moderated
    Need to write a Plsql code. Finding the difference in number of days, hours and minutes. AFAIK , there is no simple way...or you can for the code .
  • 2. Re: Difference between dates in hours
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    You're correctly getting the total number of minutes between 2 DATEs; now you just need to spearate that into hours and left-over minutes.

    Here's one way:
    WITH     got_run_minutes     AS
    (
         SELECT  (completion_date - start_date) * 24 * 60     AS run_minutes
         FROM     xx_dba_programs
    )
    SELECT     TRUNC (run_minutes / 60) || ' Hours, ' ||
         MOD   (run_minutes,  60) || ' Minutes'          AS run_time
    FROM     got_run_minutes
    ;
  • 3. Re: Difference between dates in hours
    Karthick_Arp Guru
    Currently Being Moderated
    http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
  • 4. Re: Difference between dates in hours
    884519 Newbie
    Currently Being Moderated
    Thanks a lot for quick response.

    Can you please give the complete SELECT statement?

    I need help the below statement:

    SELECT trunc((SYSDATE - ACTUAL_START_DATE) * 24,2) into v_run_time
    FROM fnd_concurrent_requests
    WHERE request_id =v_cur_request_id;

    Thanks,
    gvk.
  • 5. Re: Difference between dates in hours
    Manik Expert
    Currently Being Moderated
    Customize this way .. untested from my side.
    WITH t AS (SELECT (SYSDATE - (ACTUAL_START_DATE)) txt FROM DUAL)
    SELECT    EXTRACT (HOUR FROM txt)
           || ' Hours '
           || EXTRACT (MINUTE FROM txt)
           || ' Minutes '
           || EXTRACT (SECOND FROM txt)
           || ' Seconds '
      FROM t;
    Cheers,
    Manik.
  • 6. Re: Difference between dates in hours
    Rafu Journeyer
    Currently Being Moderated
    How about changing your v_run_time to a interval day to second datatype.
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions009.htm#SQLRF52084
    You get also seconds and days as a result.
    with fnd_concurrent_requests 
        as 
        (
         select to_date('06-01-2013 09:02:03', 'dd-mm-yyyy hh24:mi:ss' ) actual_start_date from dual union all 
         select to_date('07-01-2013 09:02:04', 'dd-mm-yyyy hh24:mi:ss' ) actual_start_date from dual union all 
         select to_date('07-01-2013 10:34:23', 'dd-mm-yyyy hh24:mi:ss' ) actual_start_date from dual 
        )
      select (systimestamp - actual_start_date) day to second(0) run_time
        from fnd_concurrent_requests
    ;
    
    RUN_TIME
    ------------
    +01 01:52:02
    +00 01:52:01
    +00 00:19:42
  • 7. Re: Difference between dates in hours
    884519 Newbie
    Currently Being Moderated
    Can someone please help here?

    I need it in XX hours, XX Minutes only.

    Output should be like (e.g) :- *06 Hours, 36 Minutes*

    Thanks,
    gvk.
  • 8. Re: Difference between dates in hours
    Rafu Journeyer
    Currently Being Moderated
    Alternatives
    select extract(HOUR from rt)
                ||' Hours, '
                ||extract(minute from rt)
                ||' Minutes' only_hours_run_time,
          (extract(day from rt)*24+extract(HOUR from rt))
                ||' Hours, '
                ||extract(minute from rt)
                ||' Minutes' days_included_run_time
    from (
      select (systimestamp - actual_start_date) rt
        from fnd_concurrent_requests
    )
    ;
  • 9. Re: Difference between dates in hours
    884519 Newbie
    Currently Being Moderated
    Hey! Thanks a lot.

    Works like a charm :).

    gvk.
  • 10. Re: Difference between dates in hours
    884519 Newbie
    Currently Being Moderated
    but the values it returns ar wrong. :(
  • 11. Re: Difference between dates in hours
    884519 Newbie
    Currently Being Moderated
    wrong values.
  • 12. Re: Difference between dates in hours
    Rafu Journeyer
    Currently Being Moderated
    Provide a test case that gives you wrong values. Tools to provide such you can find in this thread. No additional help available before you show and explain what is wrong with earlier solutions.
  • 13. Re: Difference between dates in hours
    884519 Newbie
    Currently Being Moderated
    Hi Rafu,

    I ran some programs which completed in few seconds

    e.g below:

    select trunc((actual_completion_date - actual_start_date) * 24 * 60*60, 2) Seconds
    from fnd_concurrent_requests
    where request_id = 35757573


    But the query you provided returns as 6 Hours, 3 Minutes which in fact completed in few seconds only. (I tried with both SYSDATE and ACTUAL_COMPLETION_DATE)

    Thanks,
    gvk.
  • 14. Re: Difference between dates in hours
    Rafu Journeyer
    Currently Being Moderated
    You are not showing us the test data. The query giving wrong results is also only on your screen. Provide also the desired exact result.
    with fnd_concurrent_requests 
        as 
        (
         select to_date('06-01-2013 09:02:03', 'dd-mm-yyyy hh24:mi:ss' ) actual_start_date from dual
        )
    select 
          (extract(day from rt)*24+extract(HOUR from rt))
                ||' Hours, '
                ||extract(minute from rt)
                ||' Minutes' days_included_run_time
    from (
      select (systimestamp - actual_start_date) rt
        from fnd_concurrent_requests
    )
    ;
    
    27 Hours, 4 Minutes
    To create the test case following might be used.
    select actual_completion_date,actual_start_date,sysdate
    from fnd_concurrent_requests
    where request_id = 35757573
    ;
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points