Summing time part of datetime field

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

Summing time part of datetime field

Author

Topic

NEVerton
Starting Member

9 Posts

Posted - 2012-02-15 : 18:59:13

Can anyone advise on the best way to sum the time part of a datetime field? Below is a sample select statement in which i have written sum(travel_time) - this is just to highlight where i want the sum as i know it is not possible to sum a datetime field.
Please scroll to 'Target Result' to see what the outcome i am seeking. Cheers!!

*********************************************************************

Example.

select
Call_Ref as [Call Ref]
,(select sum(travel_time) from call_events where CALL_EVENTS.link_to_call = CALLS.call_ref) as [Sum of Travel Time]

from CALLS

*********************************************************************

Sample Data

Call_Ref Travel Time
001 1900-01-01 03:00:00.000
001 1900-01-01 03:15:00.000
001 1900-01-01 03:30:00.000
002 1900-01-01 01:30:00.000

*********************************************************************

Target Result

Call_Ref Sum of Travel Time
001 1900-01-01 09:45:00.000
002 1900-01-01 01:30:00.000

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2012-02-15 : 21:51:56

[code]select
total_tm = dateadd(ss,sum(datediff(ss,0,tm)),0)
from
( -- Test Data
select tm = convert(datetime,'1900-01-01 03:00:00.000') union all
select tm = convert(datetime,'1900-01-01 03:15:00.000') union all
select tm = convert(datetime,'1900-01-01 03:30:00.000')
) a[/code]
Results:
[code]total_tm
-----------------------
1900-01-01 09:45:00.000[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources