calculate sum of time field - 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

General SQL Server Forums

New to SQL Server Programming

calculate sum of time field

Author

Topic

sajitha
Starting Member

10 Posts

Posted - 2012-07-27 : 07:15:18

Hi,
I just wonder how I can get sum of clock in time field.
Clock_in

16:39:53
14:00:20
14:00:20
14:00:20
14:00:20
09:56:29
NULL

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 07:22:34

What would be your expected output for the example you have shown here? And what data type is it?

If your data type is DATETIME, you can use a hack like this - it IS a hack, but nothing else simpler comes to mind right now. From the result, you will need to parse out the days, hours, minutes etc.

SELECT CAST(SUM(CAST(yourDateTimeColumn AS FLOAT)) AS DATETIME) FROM YourTable

sajitha
Starting Member

10 Posts

Posted - 2012-07-27 : 07:48:11

Thank you for quick reply.
My data field type is time and I expect result as hh:mm:ss format

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-27 : 08:02:04

Ok. Here is an example, again using the same hack. I am hoping someone else will have a better solution than this, but this does work:

CREATE TABLE #tmp(d TIME);
INSERT INTO #tmp VALUES ('14:00:20'),('16:39:53'),('16:39:53')

SELECT DATEDIFF(dd,0,col1) AS Days, CAST(col1 AS TIME) AS HoursMinutesSeconds
FROM
(
	SELECT CAST(SUM(CAST(CAST(d AS DATETIME) AS FLOAT)) AS DATETIME) AS col1
	FROM #tmp
) s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-27 : 10:16:19

[code]
declare @test table
(
timeval time
)

insert @test
values('16:39:53'),
('14:00:20'),
('14:00:20'),
('14:00:20'),
('14:00:20'),
('09:56:29'),
(NULL)

SELECT EffTime/(24*60*60) AS days,
CAST(DATEADD(ss,EffTime%(24*60*60),0) AS time) AS timepart
FROM
(
SELECT SUM(DATEDIFF(ss,0,timeval)) AS EffTime
FROM @test
)t

days timepart
3 10:37:42.0000000

for more idea on date operations refer

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources