SQL & PL/SQL

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 .

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
;

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129

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.

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.

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

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.

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
)
;

Hey! Thanks a lot.

Works like a charm :).

gvk.

but the values it returns ar wrong. :(

wrong values.

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.

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.

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
;

Thanks all.

I used below query:

select floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) ||
' HOURS ' ||
floor((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) -
floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60) ||
' MINUTES ' ||
round((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) -
floor(((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600 -
(floor((((f.actual_completion_date - f.actual_start_date) * 24 * 60 * 60) -
floor(((f.actual_completion_date -
f.actual_start_date) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) ||
' SECS ' time_difference
from fnd_concurrent_requests
where phase_code = 'C'
and request_id = &req_id;