SUM of Time Duration - 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 2005 Forums

Transact-SQL (2005)

SUM of Time Duration

Author

Topic

rn5a
Starting Member

25 Posts

Posted - 2008-10-13 : 07:41:39

A SQL Server DB table has a column named Duration whose datatype is varchar. Assume that 3 records under this column are (in hours & minutes) 25:30, 31:30, 16:00. The sum of these 3 records should give me 73:00 i.e. 73 hours & 00 minutes. How do I find the sum?

I could manage to get the hours using this query:

select sum(cast(substring(Duration, 0, charindex(':',Duration)) as int)) as hours from table1

Thanks,

Ron

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 07:45:12

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107275

E 12°55'05.63"
N 56°04'39.26"

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-10-14 : 05:54:49

quote:
Originally posted by rn5a

A SQL Server DB table has a column named Duration whose datatype is varchar. Assume that 3 records under this column are (in hours & minutes) 25:30, 31:30, 16:00. The sum of these 3 records should give me 73:00 i.e. 73 hours & 00 minutes. How do I find the sum?

I could manage to get the hours using this query:

select sum(cast(substring(Duration, 0, charindex(':',Duration)) as int)) as hours from table1

Thanks,

Ron

try with this.

declare @a int
select @a = sum(cast(substring(Duration, charindex(':',Duration)+1,len(duration))as int)) from table1
select cast(sum(cast(substring(Duration, 0, charindex(':',Duration))as int))+
(@a/60) as varchar) +':'+ cast(case cast(@a-((@a)/60)*60 as varchar) when '0' then '00'
else cast(@a-((@a)/60)*60 as varchar) end as varchar) from table1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 06:01:21

[code]SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theSeconds, '19000101'), 8), 1, 2, CAST(theSeconds / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(DATEDIFF(SECOND, '00:00', Duration))) AS theSeconds
FROM Table1
) AS t[/code]

E 12°55'05.63"
N 56°04'39.26"

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2008-10-14 : 06:13:19

quote:

Originally posted by Peso

SELECT	STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theSeconds, '19000101'), 8), 1, 2, CAST(theSeconds / 3600 AS VARCHAR(12)))
FROM	(
		SELECT	ABS(SUM(DATEDIFF(SECOND, '00:00', Duration))) AS theSeconds
		FROM	Table1
	) AS t

E 12°55'05.63"
N 56°04'39.26"

this is great... thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-14 : 06:40:01

If you store data as "26:10" ie over 24 hours, this will work

DECLARE	@Sample TABLE
	(
		data CHAR(5)
	)

INSERT	@Sample
SELECT	'25:30' UNION ALL
SELECT	'31:30' UNION ALL
SELECT	'16:00'

SELECT	STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM	(
		SELECT	ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
			ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes
		FROM	@Sample
	) AS d

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources