Calculate Total time based on Time Deatils

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	Hi, You can use this function, first you need to calculate total seconds like ((hrs3600)+(mins60)+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

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

0	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

0	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

0	hi..............................great job Brassbuilder wholesale hardware Gopal_nivas, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts