Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-09
7
Medium Priority
?
374 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 60

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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

927 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