Monday, May 4, 2009

how to sum time Value in Sql server.

In sql server we can't sumup time Using sum function. we have to make some stuff for summing up time value in sql server. we can do this by replacing ":" from time by "." and then treating time as Float. then sumup hours and minute individualy and then.after Convert munites to hour and add them to calculated hour..

this is tabel whose time column need to be sumup..













this is the result..








ans this is the sql script.


declare @tmpTable Table(AH varchar(20))
insert into @tmpTable values('7:30')
insert into @tmpTable values('2:30')
select * from @tmpTable
declare @Minutes as float
declare @Hours as float
declare @HoursFromMunite as Float
select @Hours=sum(cast (substring(AH,0,CHARINDEX(':',AH)) as int)),@Minutes= sum(cast( substring(AH,CHARINDEX(':',AH)+1,len(AH)) as int)) from @tmpTable
set @HoursFromMunite=Floor(@Minutes/60)
set @Hours=@Hours+@HoursFromMunite
if(@HoursFromMunite>0)
set @Minutes= ((@Minutes/60)-@HoursFromMunite)*60
select cast(@Hours as varchar(5))+':'+cast(@Minutes as varchar(5)) as Total


Thanks.