sum hours minutes seconds

whargra
whargra used Ask the Experts™
on
I am trying to sum the following HH:MM:SS and not sure of the best approach.
The below column is in a varchar not datetime datatype.

TotalHrs
83:26:55
11:44:28
34:12:07
06:48:21
00:00:07

So the sum will end being 136:11:58 I think.
Then will need to divide the minutes by 60 to get the fraction of the hour so 136.2.
Probably want this to end up as a Float datatype.

Thanks for any ideas


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hiiiii try this savefiledialog is pretty easy ,check this out.

Dim xlApp As New Application
        Dim xlWorkBook As Workbook
        Dim xlWorkSheet As Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        Dim cols As Integer
        cols = DataGridView1.Columns.Count
        Dim iRow As Integer, iCol As Integer
        For iRow = 0 To DataGridView1.Rows.Count - 1
            For iCol = 0 To DataGridView1.Columns.Count - 1
                xlWorkSheet.Cells(iRow + 1, iCol + 1).Value = DataGridView1.Columns(iCol).Name.ToString().ToUpper()
            Next
        Next
        For iRow = 0 To DataGridView1.Rows.Count - 1
            For iCol = 0 To DataGridView1.Columns.Count - 1
                xlWorkSheet.Cells(iRow + 2, iCol + 1).Value = DataGridView1.Rows(iRow).Cells(iCol).Value
            Next
        Next
        Dim Save As New SaveFileDialog
        If Save.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
        Else
            xlWorkSheet.SaveAs(Save.FileName)
            xlWorkBook.Close()
        End If


        xlApp.Quit()
        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

Open in new window

Sorry wrong post

Commented:
Try this code.

~Ajitha
            string[] dateTimes = new string[] {"83:26:55",
                            "11:44:28",
                            "34:12:07",
                            "06:48:21",
                            "00:00:07"};
            int hours=0, minutes=0, seconds = 0;
            foreach (string s in dateTimes)
            {
                string[] timeValues = s.Split(':');
                hours += Convert.ToInt32(timeValues[0]);
                minutes += Convert.ToInt32(timeValues[1]);
                seconds += Convert.ToInt32(timeValues[2]);
            }

            Console.WriteLine(Convert.ToString(hours + (minutes/60.0) + (seconds/3600.0)));

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
Thanks for the response and sorry for not being concise.

I am trying to accomplish this using SQL though.
Top Expert 2012

Commented:
Something like this perhaps:

SELECT      SUM(CAST(PARSENAME(REPLACE(TimeCol, ':', '.'), 3) AS smallint) * 3600 +
      CAST(PARSENAME(REPLACE(TimeCol, ':', '.'), 2) AS smallint) * 60 +
      CAST(PARSENAME(REPLACE(TimeCol, ':', '.'), 1) AS smallint)) / 3600.00 TimeInHours
FROM YourTableNameGoesHere
Top Expert 2012
Commented:
This is how I tested it:
DECLARE @T TABLE (TimeCol varchar(20))

SET NOCOUNT ON
INSERT @T(TimeCol) VALUES ('83:26:55')
INSERT @T(TimeCol) VALUES ('11:44:28')
INSERT @T(TimeCol) VALUES ('34:12:07')
INSERT @T(TimeCol) VALUES ('06:48:21')
INSERT @T(TimeCol) VALUES ('00:00:07')

SELECT	SUM(CAST(PARSENAME(REPLACE(TimeCol, ':', '.'), 3) AS smallint) * 3600 +
	CAST(PARSENAME(REPLACE(TimeCol, ':', '.'), 2) AS smallint) * 60 +
	CAST(PARSENAME(REPLACE(TimeCol, ':', '.'), 1) AS smallint)) / 3600.00 TimeInHours
FROM @T

Output:
TimeInHours
136.1994444

Open in new window

Author

Commented:
Thanks, acperkins, that did the trick.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial