This content has been marked as final. Show 15 replies
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,1 person found this helpful
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 ;
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
WHERE request_id =v_cur_request_id;
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;
How about changing your v_run_time to a interval day to second datatype.
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*
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 :).
but the values it returns ar wrong. :(
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.
I ran some programs which completed in few seconds
select trunc((actual_completion_date - actual_start_date) * 24 * 60*60, 2) Seconds
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)
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.
To create the test case following might be used.
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
select actual_completion_date,actual_start_date,sysdate from fnd_concurrent_requests where request_id = 35757573 ;