Adding up Time - 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)

Adding up Time

Author

Topic

mchohan
Starting Member

39 Posts

Posted - 2010-05-28 : 05:44:53

Hi
I've got 3 entries in a field:

03:05:00
02:15:00
02:05:00

Whats the best way to add these up to give me a clean value of: 07:25:00

Thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 05:55:54

[code]
select
convert(varchar(20),
(convert(datetime,'03:05:00') +
convert(datetime,'02:15:00') +
convert(datetime,'02:05:00')),108)

-- gives 07:25:00

select
convert(varchar(20),
(convert(datetime,'23:05:00') +
convert(datetime,'02:15:00') +
convert(datetime,'02:05:00')),108)

-- gives 03:25:00 !!!
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

mchohan
Starting Member

39 Posts

Posted - 2010-05-28 : 06:06:49

thanks but i need to add it up via a sum function.
How would i format the field for using sum?
thanks.

Mitesh
www.toastbox.co.uk

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 06:11:47

What should be the result for this?
12:35:00 + 12:40:00

No, you're never too old to Yak'n'Roll if you're too young to die.

mchohan
Starting Member

39 Posts

Posted - 2010-05-28 : 06:12:59

It would need to be converted into it's total hours and mins....

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 06:33:38

I have modified a solution provided by Peso here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112441
as


SELECT	
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes + theSeconds, '19000101'), 8), 1, 2, CAST((theHours + theMinutes + theSeconds) / 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,
			    ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE right(data,2) END)) AS theSeconds
-- put your data here or make a table instead			    
		FROM	(select '12:30:55' as data union all
		         select '12:35:17' union all
		         select '07:25:00')dt
	) AS d

No, you're never too old to Yak'n'Roll if you're too young to die.

mchohan
Starting Member

39 Posts

Posted - 2010-05-28 : 06:43:06

excellent, thanks! that works a treat!

Mitesh
www.toastbox.co.uk

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 06:45:38

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 10:56:52

quote:

Originally posted by webfred


select 
convert(varchar(20),
	(convert(datetime,'03:05:00') +
	 convert(datetime,'02:15:00') +
	 convert(datetime,'02:05:00')),108)

-- gives 07:25:00

select 
convert(varchar(20),
	(convert(datetime,'23:05:00') +
	 convert(datetime,'02:15:00') +
	 convert(datetime,'02:05:00')),108)

-- gives 03:25:00 !!!

No, you're never too old to Yak'n'Roll if you're too young to die.

Just for short method

select 
convert(varchar(20),
	(convert(datetime,'03:05:00') +
	 '02:15:00' +
	 '02:05:00'),108)

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 11:01:14

Madhi you are so lazy sometimes

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-28 : 11:07:46

quote:
Originally posted by webfred

Madhi you are so lazy sometimes

No, you're never too old to Yak'n'Roll if you're too young to die.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources