Calculate Total time based on Time Deatils

Posted by Gopal_nivas under ASP.NET on 7/14/2011 | Points: 10 | Views : 2403 | Status : [Member] | Replies : 6
hi..

i was stored time details into database table mentioned below
Id duration

1 0 hrs, 0 min, 7 sec
2 0 hrs, 0 min, 26 sec
1 0 hrs, 1 min, 37 sec
1 0 hrs, 0 min, 39 sec


here i want to calculate the total time based on "ID".


i was storing the time details as string.

i want to split those time(because its like 0 hrs, 0 min, 21 sec,0 hrs, 0 min, 12 sec)
values and calculate those values in to total time through sql query itself.

is it possible.


What is the efficient way to do this.

need ur suggestions with examples

regards
gopal.s




Responses

Posted by: Vishvvas on: 7/14/2011 [Member] [MVP] HonoraryPlatinum | Points: 25

Up
0
Down
Its possible. Following example demonstrates the timespan for Date data type. In your case, please cov=nvert your string data into date type (with CONVERT or CAST)
SELECT
ID,
Hours= (TotalSeconds / 3600),
Minutes=(TotalSeconds % 3600) / 60) ,
Seconds= (TotalSeconds % 60 )
from
(
select ID, DateDiff(second, StartDate, EndDate) as TotalSeconds
from Events
)
Hope this helps.

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gopal_nivas on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
hi vishvvas..

thanks for ur reply.

bec the time details are in string . that s why i am asking. provide me the query regarding my requirement

need ur suggetsions.
regards
gopal.s

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jpchoudhari on: 7/14/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

You can use this function, first you need to calculate total seconds like ((hrs*3600)+(mins*60)+secs) and pass to this function.
Refer:http://sqlserverbuddy.blogspot.com/2011/06/hour-minute-second-between-two-date.html

Create Function dbo.UFN_HourMinuteSecond

(
@Seconds BigInt
) Returns Varchar(10)
As
Begin

Declare
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

If @Seconds >= 60
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute >= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds = IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + ':' + Cast(@Minute as Varchar) + ':' + Cast(@Seconds as Varchar)

Return (@Elapsed)
End
)


Kind Regards,
Jay

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gopal_nivas on: 7/15/2011 [Member] Starter | Points: 25

Up
0
Down
hi all..

my time values will be like this

"0 hrs, 0 min, 21 sec" . it is stored in single column only.

so how to handle this.

regards
gopal.s

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jpchoudhari on: 7/15/2011 [Member] Starter | Points: 25

Up
0
Down
Hi,

chk whether you are storing all time in same fashion and then you can use SUBSTRING function to take part of hrs,mins and secs and then cast it to int

(For substing)
http://msdn.microsoft.com/en-us/library/ms187748.aspx

Kind Regards,
Jay

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Usejoy78 on: 7/15/2011 [Member] Starter | Points: 25

Up
0
Down
hi..............................great job

Brassbuilder wholesale hardware

Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response