Post reply

Assistance needed converting seconds into hours then sum hours

  • December 11, 2015 at 8:24 am

    #104261

    Hello, All,

    I have successfully converted the column from seconds into hours, however I am receiving an error when attempting to sum those hours. Here is what I have so far..

    SELECT

    PersonName,

    PersonID,

    SUM(LEFT(CONVERT(TIME, DATEADD(ms, TIMEINSECONDS * 1000, 0)), 8))

    FROM

    PERSONHOURS

    GROUP BY

    PersonName,

    PersonID

    Adding the SUM function raises the following error and I cannot seem to figure a way around it..

    Msg 8117, Level 16, State 1, Line 9

    Operand data type varchar is invalid for sum operator.

    Any suggestions would be greatly appreciated.

  • December 11, 2015 at 8:34 am

    #1845519

    The error says it all...you can't sum up a varchar data type.

    Whichever way you decide to do the conversion you should sum the values first then convert it.

    DECLARE @myTable TABLE (secs INT)

    INSERT INTO @myTable

    VALUES (90620), (5065), (2008)

    SELECT

    SUM(secs),

    RIGHT('0' + CAST(SUM(secs) / 3600 AS VARCHAR),2) + ':' + RIGHT('0' + CAST((SUM(secs) / 60) % 60 AS VARCHAR),2) + ':' + RIGHT('0' + CAST(SUM(secs) % 60 AS VARCHAR),2)

    FROM @myTable

    Just me mindful of the method you are using. For example since you are converting into time, any values that go over 24 hours will not work. i.e. 25 hours will actually show up as 01:00:00. My example will work for anything up to 99 hours but it can easily be modified to allow for a larger number of hours.

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • December 11, 2015 at 9:35 am

    #1845537

    YB751,

    Thank you, Sir. This worked out nicely. Regarding the data type, I figured as much, but couldn't come to terms on a proper solution. I greatly appreciate your time. Cheers!

    --

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply