hh:nn short time - want to add hours together

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

hh:nn short time - want to add hours together

Author

Topic

beckybug
Starting Member

3 Posts

Posted - 2010-08-19 : 11:30:57

when adding hours for week
in field TOTHRS example 8:03 8:05 8:20 8:10 7:54, Sum of tothrs is wrong -
when accumulated time hits over 24 hrs

it restarts and this makes wrong sum total.
How can I get the field on the report to show 40:32?

Thank you so much!

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-19 : 11:32:16

quote:
Originally posted by beckybug

when adding hours for week
in field TOTHRS example 8:03 8:05 8:20 8:10 7:54, Sum of tothrs is wrong -
when accumulated time hits over 24 hrs it restarts and this makes wrong sum total.
How can I get the field on the report to show 40:32?

Thank you so much!

What is the datatype for the field TOTHRS and how are you calculating the Sum?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-19 : 11:53:02

[code]
select
Hours = convert(varchar(3),datediff(hh,0,ET_Sum))+':'+
right('00'+datename(minute,ET_Sum),2)
from
(
select
ET_Sum = dateadd(ss,sum(datediff(ss,0,ET)),0)
from

( -- Test Data
select ET = convert(datetime,'8:03') union all
select ET = convert(datetime,'8:05') union all
select ET = convert(datetime,'8:20') union all
select ET = convert(datetime,'8:10') union all
select ET = convert(datetime,'7:54')
) a
) a1[/code]
Result:
[code]Hours
------
40:32[/code]

CODO ERGO SUM

beckybug
Starting Member

3 Posts

Posted - 2010-08-19 : 13:53:06

short time date/time

beckybug
Starting Member

3 Posts

Posted - 2010-08-19 : 14:21:19

I'm sorry I'm new to this - Do I key this code in sql for query?
If so this is what it looks like? It is giving me error-

SELECT [PAYROLL TABLE TOTAL REGHRS TOTDAILYHRS Query].[WKLY1], [PAYROLL TABLE TOTAL REGHRS TOTDAILYHRS Query].[TOTDAILYHRS], [PAYROLL TABLE TOTAL REGHRS TOTDAILYHRS Query].[Sum Of TOT], [PAYROLL TABLE TOTAL REGHRS TOTDAILYHRS Query].[Sum Of TOREGHRS], Hours = convert(varchar(3),datediff(hh,0,ET_Sum))+':'+
right('00'+datename(minute,ET_Sum),2)
FROM PAYROLL TABLE TOTAL REGHRS TOTDAILYHRS QUERY];

select
ET_Sum = dateadd(ss,sum(datediff(ss,0,ET)),0)
FROM PAYROLL TABLE TOTAL REGHRS TOTDAILYHRS Query];
Thanks and sorry for my ignorance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources