1 2 Previous Next 15 Replies Latest reply: Jan 7, 2013 11:45 PM by 884519 RSS

    Difference between dates in hours

    884519
      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
          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
            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
              http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
              • 4. Re: Difference between dates in hours
                884519
                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
                  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
                    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
                      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
                        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
                          Hey! Thanks a lot.

                          Works like a charm :).

                          gvk.
                          • 10. Re: Difference between dates in hours
                            884519
                            but the values it returns ar wrong. :(
                            • 11. Re: Difference between dates in hours
                              884519
                              wrong values.
                              • 12. Re: Difference between dates in hours
                                Rafu
                                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
                                  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
                                    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