How to calculate time (hours) between two dates??

[cognoshelp@ymail.com]

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

[m23]

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;