Post reply

Sum time which is in time format

  • January 25, 2011 at 8:09 am

    #232635

    Hello,

    I am having issues with calculating sum of time which is time format.

    The regular sum does not seem to work.

    Any help is appreciated.

    Thanks

  • February 3, 2011 at 2:35 pm

    #1282170

    I could get to sum time with the following expression

    =TimeSpan.FromTicks(Sum(Fields!TotalTime.Value))

  • September 7, 2012 at 3:46 am

    #1534970

    I have a feild in a table of type varchar that contains time as

    hhh:mm:ss

    e.g 73:59:59 or 144:44:59

    What I want is to get time in hours, can anyone help me write a query???

  • September 12, 2012 at 5:11 am

    #1536646

    CREATE FUNCTION fnReturnTotalHoursFromTime(

    @p varchar(14)

    )

    RETURNS decimal(20,6)

    AS

    BEGIN

    DECLARE @hoursColon tinyint, @hours decimal(12,6), @minsColon tinyint, @mins decimal(6,4), @seconds decimal (8,6)

    SELECT @hoursColon = CHARINDEX(':',@p) --Find 1st colon

    SELECT @hours = SUBSTRING(@p,1, @hoursColon -1) --Take all data before the first colon

    SELECT @minsColon = CHARINDEX(':', @p, @hoursColon +1 ) --Find position of second colon

    SELECT @mins = SUBSTRING(@p, @hoursColon +1, @minsColon - @hoursColon -1 ) --Take everything between first and second colon

    SELECT @seconds = SUBSTRING(@p, @minsColon + 1, len(@p) - @minsColon) --Find seconds

    RETURN (@hours) + (@mins / 60) + (@seconds / 3600)

    END

    Then slot that into your select query / stored procedure: SELECT dbo.fnReturnTotalHoursFromTime('4445:44:55') => 4445.748611

  • September 12, 2012 at 8:33 pm

    #1536987

    qamar 52306 (9/7/2012)

    I have a feild in a table of type varchar that contains time as

    hhh:mm:ss

    e.g 73:59:59 or 144:44:59

    What I want is to get time in hours, can anyone help me write a query???

    Whole hours? Decimal hours? Hours rounded to the nearest minute, second or ???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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