CalculateTime Difference 1

[rekhatr10]

Hi everyone,

SQLServer 2000
cyrstal 7.0
I have to calculate the time endtime to starttime.
eg. 10:01 AM 10:32 AM

data type is varchar
I typein {clroom.endtime} - {clroom.startime} I get an error like this a number, currency amount or date is requeired here. I just don't know how to proceed. I have looked at the various FAQ and followed the steps but nothing has worked out. Please help

Thank you
Rekha

 

[vidru]

Try using the CTime() function to convert your time string to an actual time. This returns 31.00:
[tt]
(CTIME("10:32 AM") - CTIME("10:01 AM"))/ 60
[/tt]
So your formula would be:
[tt]
(CTIME({clroom.endtime}) - CTIME({clroom.starttime}))/ 60
[/tt]
-dave

 

[rekhatr10]

Dave,

I can't use ctime function you think that is not avalibale in 7.0?

Rekha

 

[vidru]

Looks like CTime() became available with CR8. Try something like this instead:
[tt]
StringVar StartField := {clroom.starttime};
StringVar EndField := {clroom.endtime};
TimeVar End := Time(Val(EndField[1 to 2]), Val(EndField[4 to 5]), 0);
TimeVar Start := Time(Val(StartField[1 to 2]), Val(StartField[4 to 5]), 0);

(End - Start) / 60;
[/tt]
The above is dependent on your time fields always being in the format HH:MM.

-dave

 

[rekhatr10]

Dave,

It worked perfect. I was just wondering how do you conver it back to display it in time format. I was trying various option and it won't work guess due to the fact that it is hh:mm and it does not have seconds which is giving trouble.

Thank you for your help
REkha

 

[vidru]

Replace the last line of the formula with this to return the difference as Time. You can then format the formula field to get rid of AM/PM, set 24 hour time format, etc.
[tt]
NumberVar TotalMinutes := (End - Start) / 60;
NumberVar Hours := Truncate(TotalMinutes / 60);
NumberVar Minutes := Remainder(TotalMinutes, 60);
Time(Hours, Minutes, 0);
[/tt]
-dave

 

[rekhatr10]

Dave,


Worked like a charm. Thank you so much.