Help with DateTime Calculation

[tttggg]

I’m trying to get a time duration from two dates. But the results set is not correct. Can any one tell me what i/m missing here.

My formula ,

If isnull ({T_ObjectStatusLogs.ClearDT}) then
datediff ("h",{T_ObjectStatusLogs.SetDT},currentdatetime)
Else
numberVar dur := DateDiff("S",{T_ObjectStatusLogs.SetDT},{T_ObjectStatusLogs.ClearDT});
numberVar days;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar ddhhmmss;

days := truncate(Truncate(Truncate(dur/60)/60)/24);
hrs := Remainder (Truncate(Truncate(dur/60)/60),24);
min := Remainder(Truncate(dur/60),60);
sec:= Remainder (dur,60);

ddhhmmss:= totext(days,0,"")+":"+totext(hrs,"00")+":" + totext(min,"00")+":"+totext(sec,"00");
ddhhmmss

the result is,

SetDT Clear
Duration


11/30/2007 6:28:05 11/30/2007 6:32:20 0:00:04:15
11/30/2007 6:28:10 11/30/2007 6:32:25 0:00:04:15
11/30/2007 6:28:15 11/30/2007 6:32:30 0:00:04:15
11/30/2007 6:28:20 0:00:04:15
11/20/2007 8:09:23 11/30/2007 5:49:40 9:21:40:17
09/24/2007 5:19:42 09/24/2007 5:20:37 0:00:00:55

current datetime 1/30/2008 1:03:52
The issue is that there are cases where ClearDT is “ Null”
That’s why I have the IF statement in the formula.

Any help with this is much appreciated
Working with CR 10 and SQL Server.

 

[lbass]

This part:

If isnull ({T_ObjectStatusLogs.ClearDT}) then
datediff ("h",{T_ObjectStatusLogs.SetDT},currentdatetime) Else
numberVar dur := DateDiff("S",{T_ObjectStatusLogs.SetDT},{T_ObjectStatusLogs.ClearDT});

Should be:

numberVar dur := If isnull({T_ObjectStatusLogs.ClearDT}) then
datediff ("s",{T_ObjectStatusLogs.SetDT},currentdatetime) else
DateDiff("s",{T_ObjectStatusLogs.SetDT},{T_ObjectStatusLogs.ClearDT});

You need to have both evaluating seconds.

-LB

 

[tttggg]

Thanks LB i got it to work.
I have an issue with alignment. I know this has to in a different thread but i'm posting here since i might easy to follow me.
In order to make the "Duration" different where the clearDT is Null i'm added a + sign with value.
Eg: ####+ (when ClearDT is Null)
I got this working but it displays as follows,
####+
#####
#####
####+
Is it Possible to make like ,
0####+
#####
#####
0####+
Any help with this much appreciated.

 

[lbass]

Can't follow this. Please show how you did this (the contents of the formula) and show a sample of the actual resulting display.

-LB

 

[tttggg]

Thanks for the concern LB i figured it out
I just Add the "+" sign to the resulting string in the above formula.

Ddhhmmss:=totext(days,0,"")+":"totext(hrs,00,"")+":"......
+ "+"

I don't know this is good way of doing it , but its works.
If there is any better way of doing it please let me know.

Thanks