time conversions 1

[darwin101]

after all day.. i still dont have it. i am trying to compute the time between a start time and an end time. both of those started out as char's.
this converts char to the start time
TIME ( ToNumber ( {Query.Inv Start Time} [ 1 to 2 ] ) ,
ToNumber ( {Query.Inv Start Time} [ 3 to 4 ] ) ,
ToNumber ( {Query.Inv Start Time} [ 5 to 6 ] ) )

this one converts chars to time stop ('000000' was null in the DB)
IF ( {Query.Loc Stop Time} = '000000' ) Then
TIME ( ToNumber ( {Query.SYSDATE} [ 12 to 13 ] ),
ToNumber ( {Query.SYSDATE} [ 15 to16 ] ),
ToNumber ( {Query.SYSDATE} [ 18 to 19 ] ))
else
TIME ( ToNumber ( {Query.Loc Stop Time} [ 1 to 2 ] ) ,
ToNumber ( {Query.Loc Stop Time} [ 3 to 4 ] ) ,
ToNumber ( {Query.Loc Stop Time} [ 5 to 6 ] ) )

converts time to seconds (start time)
( ToNumber ( {Query.Inv Start Time} [ 1 to 2 ] ) * 360 +
ToNumber ( {Query.Inv Start Time} [ 3 to 4 ] ) * 60 +
ToNumber ( {Query.Inv Start Time} [ 5 to 6 ] ) )

converts time to seconds (stop time)
IF ( {Query.Loc Stop Time} = "000000" ) THEN
( ToNumber ( {Query.SYSDATE} [ 12 to 13 ] ) * 360 +
ToNumber ( {Query.SYSDATE} [ 15 to 16 ] ) * 60 +
ToNumber ( {Query.SYSDATE} [ 18 to 19 ] ) )
ELSE
( ToNumber ( {Query.Loc Stop Time} [ 1 to 2 ] ) * 360 +
ToNumber ( {Query.Loc Stop Time} [ 3 to 4 ] ) * 60 +
ToNumber ( {Query.Loc Stop Time} [ 5 to 6 ] ) )

trys to compute time difference
IF {Query.Loc Stop Time} < {Query.Inv Start Time} THEN
( {@T stop} + 8640 ) - {@T start}

ELSE {@Time Stop} - {@Time Start}

if the end time is larger than the start time the calculation works (hh:mm:ss).. other wise its not even close. i think this is the only place that is causing me to pull hair.

thanks for whom ever is up to helping

 

[kenhamady]

Try 86400 instead of 8640 Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[darwin101]

still not working.. here is the formula that calculates the date
Hey thanks for giving it a look.

IF {@T stop} < {@T start} THEN (
NumberVar A1= 8640 - {@T start};
numberVar A2 = 8640 - {@T stop};
NumberVar TotalSec :={@Time Diff} + A1 +A2 ;

NumberVar Hours := Truncate (TotalSec / 3600 );
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);
Totext ( Hours, '00', 0,'') + ':'+
Totext ( Minutes,'00', 0,'') + ':'+
Totext ( Seconds,'00', 0,'') ; )

Else (
//WhilePrintingRecords;
NumberVar TotalSec := {@Time Diff} ;

 

[kenhamady]

Why are you using 8640? There are 86400 seconds in a day, aren't there? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)