Sum of HH:MM:SS into just seconds

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 HH:MM:SS into just seconds

Author

Topic

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-17 : 08:52:15

There is a column named Timings in HH:MM:SS format.
Datatype of this column is varchar(50).

I want to sum the rows in this column and get the output as one single record.

00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16

Please help me with a query for this.

Thannk you.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 08:57:54

select time_col,datediff(second,0,dateadd(day,0,time_col)) from your_table

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 09:00:30

quote:
Originally posted by notes4we

There is a column named Timings in HH:MM:SS format.
Datatype of this column is varchar(50).

I want to sum the rows in this column and get the output as one single record.

00:01:06
00:01:16
00:01:04
00:01:24
00:01:13
00:01:06
00:02:21
00:01:16

Please help me with a query for this.

Thannk you.

What did you edit?
Is this?

select sum(datediff(second,0,dateadd(day,0,time_col))) from your_table

Madhivanan

Failing to plan is Planning to fail

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-17 : 09:02:06

quote:
Originally posted by madhivanan

select time_col,datediff(second,0,dateadd(day,0,time_col)) from your_table

Madhivanan

Failing to plan is Planning to fail

Thank you for your reply Madhivanan.

I am getting the output as follows:

I want to have sum of all the rows. Which means Total of the rows in HH:MM:SS format.

Can you please help me achieve that.

00:01:06 66
00:01:16 76
00:01:04 64
00:01:24 84
00:01:13 73
00:01:06 66
00:02:21 141
00:01:16 76

The answer should be somewhat like:
00:15:30, which means the total of the above columns in 15 minutes and 30 seconds.

notes4we
Yak Posting Veteran

90 Posts

Posted - 2009-04-17 : 09:06:08

select sum(datediff(second,0,dateadd(day,0,time_col))) from your_table

Looks good.

But can you help me edit the query and little to get the result in HH:MM:SS format, rather than just in Seconds. I thought seconds would be good, but it will be difficult to interpret, so I want it in HH:MM:SS format.

Thank you.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-17 : 10:22:42

Take a look at this

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 10:31:21

[code]

select convert(varchar(8),dateadd(ss,sum(datediff(second,0,dateadd(day,0,time_col))),0),108) from your_table
[/code]

zizouwa00
Starting Member

1 Post

Posted - 2010-09-28 : 08:53:09

hey all i have a lil problem if u can help me out i used
select convert(varchar(MAX),dateadd(ss,sum(datediff(second,0,dateadd(day,0,date))),0),108) from table
it worked perfectly on a small table but i have lik 3 million data in my sql database and it gives me an error
An arithmetic overflow error occurred while converting expression to data type int.
please help me out i have this project that i have to deliver tomorow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources