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

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
LapchienDirectorAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
What is the datatype of the time field? Format?
0
 
BrandonGalderisiCommented:
And what doe your current data look like?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LapchienDirectorAuthor Commented:
datatype is sql datetime
sample of data in field:
1900-01-01 00:00:58.000
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
LapchienDirectorAuthor Commented:
SELECT SUM(DatePart(ss, timecolumn)) appears to sum only the seconds part of the time?
0
 
LapchienDirectorAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.