Best way to calculate/represent a TIME difference

[dougcoulter]

I am looking to determine the time difference between two datetime datatypes. I am familiar with the DateAdd and DateDiff functions, but I was wondering what would be the best way to represent this time difference for future aggregation. As an example, let's say I want to know the elapsed time between the following datetime datatypes:

9/10/01 4:30:00 PM
9/11/01 5:15:30 PM

I need to somehow represent the time elapsed (preferably in a single field) as 1 day, 45 minutes and 30 seconds. Initially I considered just using the DateDiff function multiple times to come up with a varchar datatype in the form of '1:00:45:30' (1 day, 0 hours, 45 minutes and 30 seconds). But I then realized it would be fairly difficult to summarize multiple records of this type.

Sorry for the long-winded explanation! Any thoughts/shared experience would be greatly appreciated.

Doug

 

[fluteplr]

I would suggest retrieving the data from sql server as the datediff in seconds, and then using whatever front end or report writer you are working with to format the display into days, hours and second. That way you have the seconds easily available to total, average etc.