Substracting two date fields

building reports crystal reports
1

we are using Oracle 9i as back end d/b

aneel (BOB member since 2007-11-19)

2

How can I substract two(Start Date and End Date) dates in crystal report to get the result as “DD:HH:MM:SS”

Please help

aneel (BOB member since 2007-11-19)

3

I’m assuming you are trying to do a countdown/up timer of some sort. Here is something I put together to help you. It is a Crystal Fuction so you should just be able to paste it into a new function and replace the two DateTime parameters with your date/time fields.


local stringvar output;
local NumberVar NumSecs;  //Value of difference in seconds
NumSecs := abs(DateDiff ("s",dateTime("06/01/2009 12:00:00AM"),CurrentDateTime)); //Replace DateTime values here with values to calculate

local NumberVar RemainingSecs := remainder(NumSecs,60); //deteremine remaining seconds using remainder
NumSecs :=NumSecs-RemainingSecs; //remove remaining seconds from total

local NumberVar RemainingMinutes := remainder((NumSecs/60),60); //calculate remianing minutes
NumSecs :=NumSecs-(RemainingMinutes*60); //remove remaining minutes (converted to seconds) from total difference in seconds

local NumberVar RemaingHours := remainder(NumSecs/3600,24); //calulate remaining hours
NumSecs :=NumSecs-(RemaingHours*3600); //remove hours (converted to seconds) from total seconds

local NumberVar RemainingDays := NumSecs/86400; //We should now have an even number divisible by the number of seconds in a day to get remaining days

//Concatenate them all together to form the countdown string
output :=       right("00"+cstr(RemainingDays,0),3) +
          ":" + right("0"+cstr(RemaingHours,0),2)+
          ":" + right("0"+cstr(RemainingMinutes,0),2)+ 
          ":" + right("0"+cstr(RemainingSecs,0),2);

Hope this helps.

skeeter :us: (BOB member since 2008-05-22)