Sum time which is in time format

Question

Post reply

Sum time which is in time format

sappunni

Grasshopper

Points: 22
More actions
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

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

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

sappunni Grasshopper Points: 22 More actions · Answer 1

I could get to sum time with the following expression

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

qamar 52306 SSC Enthusiast Points: 168 More actions · Answer 2

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???

simon.johnson SSChasing Mays Points: 628 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)