Hi kishore21,
Refer the below sample code.
C#
protected void Page_Load(object sender, EventArgs e)
{
Int64 totalHours = 0, totalMinutes = 0, totalSeconds = 0;
List<string> listOfHours = new List<string>();
listOfHours.Add("1:38:00");
listOfHours.Add("1:30:02");
listOfHours.Add("1:00:59");
foreach (string timevalue in listOfHours)
{
totalHours += Convert.ToInt64(timevalue.Split(':')[0]);
totalMinutes += Convert.ToInt64(timevalue.Split(':')[1]);
totalSeconds += Convert.ToInt64(timevalue.Split(':')[2]);
}
Int64 totalTimeInSeconds = 0;
totalTimeInSeconds += ConvertToSeconds(totalHours, (60 * 60));
totalTimeInSeconds += ConvertToSeconds(totalMinutes, 60);
totalTimeInSeconds += totalSeconds;
Response.Write("Total Hours : " + ConvertToTotal(totalTimeInSeconds));
}
private Int64 ConvertToSeconds(Int64 value, Int64 multipleValue)
{
return value * multipleValue;
}
private string ConvertToTotal(Int64 totalTime)
{
return Convert.ToString(totalTime / (60 * 60)).PadLeft(2, '0') + " : "
+ Convert.ToString((totalTime % (60 * 60)) / 60).PadLeft(2, '0') + " : "
+ Convert.ToString((totalTime % (60 * 60)) % 60).PadLeft(2, '0');
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
Dim totalHours As Int64 = 0, totalMinutes As Int64 = 0, totalSeconds As Int64 = 0
Dim listOfHours As New List(Of String)()
listOfHours.Add("1:38:00")
listOfHours.Add("1:30:02")
listOfHours.Add("1:00:59")
For Each timevalue As String In listOfHours
totalHours += Convert.ToInt64(timevalue.Split(":"c)(0))
totalMinutes += Convert.ToInt64(timevalue.Split(":"c)(1))
totalSeconds += Convert.ToInt64(timevalue.Split(":"c)(2))
Next
Dim totalTimeInSeconds As Int64 = 0
totalTimeInSeconds += ConvertToSeconds(totalHours, (60 * 60))
totalTimeInSeconds += ConvertToSeconds(totalMinutes, 60)
totalTimeInSeconds += totalSeconds
Response.Write(Convert.ToString("Total Hours : ") & ConvertToTotal(totalTimeInSeconds))
End Sub
Private Function ConvertToSeconds(value As Int64, multipleValue As Int64) As Int64
Return value * multipleValue
End Function
Private Function ConvertToTotal(totalTime As Int64) As String
Return Convert.ToString(Convert.ToInt64(totalTime / (60 * 60))).PadLeft(2, "0"c) + " : " _
+ Convert.ToString(Convert.ToInt64((totalTime Mod (60 * 60)) / 60)).PadLeft(2, "0"c) + " : " _
+ Convert.ToString(Convert.ToInt64((totalTime Mod (60 * 60)) Mod 60)).PadLeft(2, "0"c)
End Function
Output
Total Hours : 04 : 09 : 01
SQL
-- Assuming data in below format in database as same as you provided.
DECLARE @HoursList As TABLE (Timevalue VARCHAR(100))
INSERT INTO @HoursList
VALUES ('01:30:00')
,('01:35:00')
,('01:08:02')
SELECT (RIGHT('00'+CAST((SUM((RIGHT('00'+SUBSTRING(Timevalue,0,CHARINDEX(':',Timevalue)),2)*60*60)
+(RIGHT('00'+SUBSTRING(SUBSTRING(TimeValue,CHARINDEX(':',TimeValue)+1,LEN(TimeValue)-CHARINDEX(':',TimeValue)),0,CHARINDEX(':',SUBSTRING(TimeValue,CHARINDEX(':',TimeValue)+1 ,LEN(TimeValue)- CHARINDEX(':',TimeValue)))),2)*60)
+ RIGHT('00'+REVERSE(SUBSTRING(REVERSE(Timevalue), 1,CHARINDEX(':', REVERSE(Timevalue))-1)),2))/(60*60))
AS VARCHAR(2))
,2)
+ CAST(':' AS VARCHAR(2))
+RIGHT('00'+CAST(((SUM((RIGHT('00'+SUBSTRING(Timevalue, 0,CHARINDEX(':', Timevalue)),2)*60*60)
+(RIGHT('00'+SUBSTRING(SUBSTRING(TimeValue,CHARINDEX(':',TimeValue) + 1 ,LEN(TimeValue)-CHARINDEX(':',TimeValue)),0,CHARINDEX(':',SUBSTRING(TimeValue,CHARINDEX(':',TimeValue)+1 ,LEN(TimeValue)- CHARINDEX(':',TimeValue)))),2)*60)
+RIGHT('00'+REVERSE(SUBSTRING(REVERSE(Timevalue), 1,CHARINDEX(':', REVERSE(Timevalue))-1)),2))%(60*60))/60)
AS VARCHAR(2))
,2)
+ CAST(':' AS VARCHAR(2))
+ RIGHT('00'+CAST(((SUM((RIGHT('00'+SUBSTRING(Timevalue, 0,CHARINDEX(':', Timevalue)),2)*60*60)
+(RIGHT('00'+SUBSTRING(SUBSTRING(TimeValue,CHARINDEX(':',TimeValue) + 1 ,LEN(TimeValue)-CHARINDEX(':',TimeValue)),0,CHARINDEX(':',SUBSTRING(TimeValue,CHARINDEX(':',TimeValue)+1 ,LEN(TimeValue)- CHARINDEX(':',TimeValue)))),2)*60)
+RIGHT('00'+REVERSE(SUBSTRING(REVERSE(Timevalue), 1,CHARINDEX(':', REVERSE(Timevalue))-1)),2))%(60*60))%60)
AS VARCHAR(2))
,2)) TotalHour
FROM @HoursList
OUTPUT