Solved

Sum of a DateTime field to give total number of seconds, or minutes, or both?

Posted on 2008-10-09
7
354 Views
Last Modified: 2010-05-18
I need a simple select query to sum a time field - so 1 minute and 5 seconds plus 30 seconds would result in a total of 1 minute and 35 seconds.

Thanks

Lapchien
0
Comment
Question by:Lapchien
  • 3
  • 3
7 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22677455
What is the datatype of the time field? Format?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22677506
And what doe your current data look like?
0
 

Author Comment

by:Lapchien
ID: 22677707
datatype is sql datetime
sample of data in field:
1900-01-01 00:00:58.000
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22677935
You can try this:

SELECT SUM(DatePart(ss, timecolumn))
FROM YourTableName

Here is a reference on datepart: http://msdn.microsoft.com/en-us/library/ms174420.aspx
0
 

Author Comment

by:Lapchien
ID: 22677982
SELECT SUM(DatePart(ss, timecolumn)) appears to sum only the seconds part of the time?
0
 

Author Comment

by:Lapchien
ID: 22678079
Think I may have answered my own question, unless you can improve on:

CONVERT(char(8), DATEADD(second, SUM(DATEDIFF(ss, 0, CONVERT(DATETIME, CallDuration, 114))), '0:00:00'), 108)

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 22678499
I meant to get you the difference in seconds, I apologize. :)

DATEDIFF(dd, 0, CallDuration) ==> gets you call duration date at midnight
DATEDIFF(ss, DATEDIFF(dd, 0, CallDuration), CallDuration) ==> Gets you difference in seconds
SUM(DATEDIFF(ss, DATEDIFF(dd, 0, CallDuration), CallDuration)) ==> gets sum in seconds which can be converted to minutes/seconds by simple math later

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Generate Scripts Fails 5 34
export sql results to csv 6 34
Simple SQL query from two tables 13 52
SQL Server - Set Value of Multiple Fields in One Query 10 22
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question