SUM OF TIME IN SQL SERVER 2008

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 IN SQL SERVER 2008

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-05 : 01:25:51

Iam having a table with column name TotalHrs. Its data type is sys.time.
i.e. in the format of HH.MM.SS.MILLISECONDS

I want to sum the column TotalHrs.

00:56:30.0000000
02:08:40.0000000
01:01:00.0000000

and get the o/p like this.

O/P ==> 04:05:10.0000000

How can.

The query that I had wrote

SELECT SUM(VRL.TotalHrs)
FROM VRL
WHERE VRL.DateDt = '3/5/2009'

I got the error message

Operand data type time is invalid for sum operator.

Thanks in advance.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-05 : 01:28:57

The sum Operator is not work on varchar type datatypes ,check it once

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-05 : 02:05:33

quote:
Originally posted by Nageswar9

The sum Operator is not work on varchar type datatypes ,check it once

Hi,

The my column datatype is time, not varchar.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-05 : 02:07:25

Try

select cast(dateadd(millisecond,sum(datediff(millisecond,0,cast(totalhrs as datetime))),0) as time) from table

Madhivanan

Failing to plan is Planning to fail

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-05 : 03:25:22

quote:
Originally posted by madhivanan

Try

select cast(dateadd(millisecond,sum(datediff(millisecond,0,cast(totalhrs as datetime))),0) as time) from table

Madhivanan

Failing to plan is Planning to fail

Thanks it worked for me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-05 : 08:53:37

quote:
Originally posted by baburk

quote:
Originally posted by madhivanan

Try

select cast(dateadd(millisecond,sum(datediff(millisecond,0,cast(totalhrs as datetime))),0) as time) from table

Madhivanan

Failing to plan is Planning to fail

Thanks it worked for me

You are welcome

Madhivanan

Failing to plan is Planning to fail

vingo_mail
Starting Member

1 Post

Posted - 2009-03-23 : 02:57:18

The above query which u replied worked only for only within a time of a date. Do u have any idea to add only time values. I meant the sample like below

Time
HH:MM:SS
==========
12:01:00
18:32:00
12:30:00
-----------
43:03:00 --------> Total Sum like it

But your query gives a result : 19:02:00

Please Let me know how to get the above model output

Thanks for advance

liang.??
Starting Member

3 Posts

Posted - 2009-03-23 : 07:09:09

[code]
---------------------------------
-- Author: liangCK ??
---------------------------------

--> ??????: @T
DECLARE @T TABLE (Time VARCHAR(20))
INSERT INTO @T
SELECT '12:01:00' UNION ALL
SELECT '18:32:00' UNION ALL
SELECT '12:30:00'

--SQL????:

SELECT Time FROM @T
UNION ALL
SELECT
RIGHT(100+SUM(CAST(PARSENAME(REPLACE(Time,':','.'),3) AS INT))
+SUM((CAST(PARSENAME(REPLACE(Time,':','.'),2) AS INT)
+CAST(PARSENAME(REPLACE(Time,':','.'),1) AS INT)))/60,2)
+':'
+RIGHT(100+SUM((CAST(PARSENAME(REPLACE(Time,':','.'),2) AS INT)
+CAST(PARSENAME(REPLACE(Time,':','.'),1) AS INT)))%60,2)
+':'
+RIGHT(100+SUM(CAST(PARSENAME(REPLACE(Time,':','.'),1) AS INT))%60,2)
FROM @T

/*

Time
--------------------
12:01:00
18:32:00
12:30:00
43:03:00

(4 ????)*/
[/code]

orashid
Starting Member

3 Posts

Posted - 2011-05-11 : 19:36:20

quote:
Originally posted by vingo_mail

The above query which u replied worked only for only within a time of a date. Do u have any idea to add only time values. I meant the sample like below

Time
HH:MM:SS
==========
12:01:00
18:32:00
12:30:00
-----------
43:03:00 --------> Total Sum like it

But your query gives a result : 19:02:00

Please Let me know how to get the above model output

Thanks for advance

I have no idea whether you got soultion for this or not.
Try This

Sum(datepart(hh,Cast([YourTime] as Datetime)))+(sum(datepart(MINUTE,Cast([YourTime] as Datetime)))/60)+(sum(datepart(MINUTE,Cast([YourTime] as Datetime)))%60+(sum(datepart(SECOND,Cast([YourTime] as Datetime)))/60))/60
,(sum(datepart(MINUTE,Cast([YourTime] as Datetime)))%60+(sum(datepart(SECOND,Cast([YourTime] as Datetime)))/60))%60
,sum(datepart(SECOND,Cast([YourTime] as Datetime)))%60

[Your Time] will be your column of Data Type Time or it can be a string in form "hh:mm:ss".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources