Convert String 1:45 to seconds 1

[jrball]

I have a colum in an SQL db that is the total time that it takes someone to complete an online assessment, but it's stored as a string. An example would be 1:45. So it took them one minute forty five seconds to complete the test.

How do I convert that to a number first then to total seconds?

The column name is {ComputerBasicsAssessment.OverallTime}

 

[jrball]

I figured it out...
(ToNumber(split({ComputerBasicsAssessment.OverallTime},":")[1])*60)+ToNumber(split({ComputerBasicsAssessment.OverallTime},":")[2])

 

[jrball]

half way there.. I need to take an average of all the results and display that also.

I have the variables averaged to seconds, but now I need to convert the seconds back to the MM:SS format to display..

For example one average is 76 seconds..how do I get it to display 1:16 ?

 

[lbass]

I think this is adapted from a SynapseVampire FAQ:

whileprintingrecords;
numberVar secs := {@average};
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(secs/60)/60);
min := Remainder(Truncate(secs/60),60);
sec := Remainder(secs,60);

hhmmss := totext(hrs, "00") + ":" +
totext(min, "00") + ":" + totext(sec, "00");

hhmmss;

-LB

 

[jrball]

Works Great, Thanks LB!