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
Medium Priority
374 Views
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
Question by:Lapchien
• 3
• 3

LVL 60

Expert Comment

ID: 22677455
What is the datatype of the time field? Format?
0

LVL 39

Expert Comment

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

Author Comment

ID: 22677707
datatype is sql datetime
sample of data in field:
1900-01-01 00:00:58.000
0

LVL 60

Expert Comment

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

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

Author Comment

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

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

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
###### Suggested Courses
Course of the Month9 days, 16 hours left to enroll