Solved

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

Posted on 2008-10-09
7
346 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
Comment Utility
What is the datatype of the time field? Format?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
Comment Utility
And what doe your current data look like?
0
 

Author Comment

by:Lapchien
Comment Utility
datatype is sql datetime
sample of data in field:
1900-01-01 00:00:58.000
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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
Comment Utility
SELECT SUM(DatePart(ss, timecolumn)) appears to sum only the seconds part of the time?
0
 

Author Comment

by:Lapchien
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now