Hello all gurus here,
I need to calculate hours between two dates..
What I need is I have date column in Database which I need to subtract with sysdate the result I need to get is in hours
e.g 07/26/2008 12:14:20 is my Database time Sysdate is 07/27/2008 10:20:15 then my result should be
07/27/2008 10:20:15 - 07/26/2008 12:14:15 = 21:55:00 hrs
What function do I need use....Please give complete calculation
I need ASAP...Thanks
First I think you've made a mistake in your calculation
07/27/2008 10:20:15 - 07/26/2008 12:14:15 =22:6:0 hrs not 21:55:00 hrs.
Since it seems you are using oracle you could create a pl/sql function to calculate it for you.
Then in Report Studio you would put my_date_diff(to_date('07/27/2008 10:20:15','MM/DD/YYYY HH:MI:SS'),to_date('07/26/2008 12:14:15','MM/DD/YYYY HH:MI:SS'))
You can replace those dates with sysdate if you like. The function definition I have modified an existing one from http://www.quest-pipelines.com/newsletter-v3/1202_C.htm#two. You could put the logic in the function into a report studio column if you wanted.
CREATE OR REPLACE
function my_date_diff(p_dte1 IN DATE, p_dte2 IN DATE) return varchar2
IS
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
BEGIN
v_diff := ABS(p_dte2 - p_dte1);
v_hrs := TRUNC(v_diff, 0)*24; -- start with days portion if any
v_diff := (v_diff - TRUNC(v_diff, 0))*24; -- lop off whole days, convert
to hrs
v_hrs := v_hrs + TRUNC(v_diff, 0); -- add in leftover hrs if any
v_diff := (v_diff - TRUNC(v_diff, 0))*60; -- lop off hrs, convert to mins
v_min := TRUNC(v_diff, 0); -- whole mins
v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0); -- lop off mins,
return(
TO_CHAR(v_hrs) || ':' ||
TO_CHAR(v_min) || ':' ||
TO_CHAR(v_sec));
END print_date_diff;