how to return difference in datetime values as a timespan

[new DBA in '09]

Hello,

DATEDIFF returns an integer, which I suppose I could use to build a
timespan string, but I'd like to avoid reinventing any wheels. How do
I retun the difference in two datetime values as a "timespan" value
that won't completely confuse the report user? To me, "02:14:37"
represents two hours, fourteen minutes, and 37 seconds. For example,
what's the best way to determine the NapLength value in the following
query results?

DECLARE @NapTimes TABLE (
NapID INT
, StartTime DATETIME
, EndTime DATETIME
)

INSERT INTO @NapTimes SELECT 1, getdate(), dateadd(minute, 75,
getdate())
UPDATE @NapTimes SET EndTime = dateadd(second, 29, EndTime)

SELECT *, '1:15:29' AS NapLength FROM @NapTimes

[new DBA in '09]

Well it took me about .5 minutes to figure this out on my own.

declare @D1 datetime, @d2 datetime, @d3 time(0)

select @D1 = GETDATE(), @d2 = dateadd(hour, 2, GETDATE())
select @d2 = DATEADD(minute, 14, @d2)
select @d2 = DATEADD(second, 32, @d2)
select @d3 = @d2 - @d1

select @D1
select @d2
select @d3 --returns "02:14:32"

[Tom Cooper]

There is no build in function to do that. You have to do your own. There
are lots of ways, for example

Select NapID, CONVERT(char(8), DATEADD(second, DATEDIFF(second, StartTime,
EndTime), '19000101'), 114)
From @NapTimes;

Select NapID, LEFT(CAST(DATEADD(second, DATEDIFF(second, StartTime,
EndTime), '19000101') As Time), 8)
From @NapTimes;

The first one works on any version of SQL, the second on SQL 2008 or later).
They assume the time difference is less than 24 hours. If you need to do
this in lots of places, you might want to consider a user defined function.

But the best solution (IMO) is to just return the difference in seconds to
your front end, and have your front end format it. While you can use SQL to
format data, it generally works beetter to do it in the front end.

Tom

"new DBA in '09" <ericb...@gmail.com> wrote in message
news:76f30cf1-24dc-40c3...@l20g2000yqm.googlegroups.com...

[new DBA in '09]

Thanks, Tom, that works stupendously. I like your idea about the
formatting being done on the front end. Especially considering that
my own solution above won't account for naps that actually span a date
change, but would end up giving a result like "21:46:32". Which makes
me look bad. Thanks for saving me the embarassment.