sum of smalldatetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sum of smalldatetime

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-02-16 : 04:00:35

how to sum all timeelapsed with a datatype of smalldatetime?

field1---TimeElapsed
1--------1:2:8
1--------2:0:0
1--------2:0:0
1--------2:0:0
2--------2:0:0

result..
field1---TimeElapsed
1--------7:2:8
1--------2:0:0

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-16 : 05:13:53

select field1,convert(varchar(2),sum(convert(numeric,parsename(replace(convert(varchar(12),TimeElapsed,114) ,':','.'),4))))
+':'+ convert(varchar(2),sum(convert(numeric,parsename(replace(convert(varchar(12),TimeElapsed,114) ,':','.'),3))))
+':'+ convert(varchar(2),sum(convert(numeric,parsename(replace(convert(varchar(12),TimeElapsed,114) ,':','.'),2)))) from table group by field1

Madhivanan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-16 : 05:18:05

emmm.... how bout:


select 	field1, 
	convert(varchar(5), sum(datepart(hh, TimeElapsed))) + ':' + 
	convert(varchar(5), sum(datepart(n, TimeElapsed))) + ':' + 
	convert(varchar(5), sum(datepart(s, TimeElapsed))),
        sum(datepart(s, TimeElapsed) + datepart(n, TimeElapsed)*60 + datepart(hh, TimeElapsed)*3600)	-- this gives sum of seconds
from 	MyTable
group by field1

keep in mind this will not put 60 seconds to a minute or mins to hours

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources