Calculate time between two dates in hours:minutes:seconds.

Trying to use datediff drove me nuts and is ok of you have two dates from the same day. But try calculating the time for an index build for example that occurs 1 second either side of midnight. The Datediff for day, hour, minute all show 1. The only true value I found is to use Datediff on second between the two dates and then calculate the hours, minutes and seconds based on this value. Proc code below. Anybody see any issues? Thanks Create procedure [dbo].[DWS_sp_CalcTimeTaken] @StartDateTime
Datetime, @EndDateTime Datetime
, @Duration varchar(10) output
AS/******
Date: 07-25-2008
Version: 1.2
DESC: Calculate time taken from a supplied Start and End time and give result as 00:00:00
******/DECLARE
@Hours varchar(2), @Minutes varchar(2), @Seconds varchar(2), @TotalSeconds varchar(10)SELECT
@TotalSeconds = DATEDIFF(second, @StartDateTime,@EndDateTime)-- CALCULATE_HOURSSELECT
@Hours = @TotalSeconds / (60*60) -- we are chopping off the rest.IF
(Select LEN(@Hours)) = 1 BEGIN
Select @Hours = '0' + @HoursEND-- CALCULATE_MinutesSELECT
@Minutes = (@TotalSeconds - (@Hours * (60*60))) / 60IF
(Select LEN(@Minutes)) = 1 BEGIN
Select @Minutes = '0' + @MinutesEND-- CALCULATE_SecondsSELECT
@Seconds = @TotalSeconds - ((@Minutes * 60) + (@Hours * (60*60)))IF
(Select LEN(@Seconds)) = 1 BEGIN
Select @Seconds = '0' + @SecondsEND
select @Duration = @Hours + ':' + @Minutes + ':' + @Seconds
----------------------- Run the proc -----set nocount on Declare@StartDateTime Datetime
, @EndDateTime Datetime
Select @StartDateTime = '2008-07-27 23:59:59.000'Select
@EndDateTime = '2008-07-28 00:00:01.000'Declare @Duration varchar(10)
EXEC [DWS_ETL].[dbo].[DWS_sp_CalcTimeTaken] @StartDateTime, @EndDateTime, @Duration output
Select @Duration

You should anticipate the interval to exceed 100 hours, so don't limit the hours to two characters.
This may be a little shorter than what you have:
declare @start datetime, @end datetime, @duration varchar(36),
@hours varchar(30), @minutes varchar(2), @seconds varchar(30)
set @start = .............
set @end = .............

set @seconds = abs(datediff(second, @start, @end))
set @hours = @seconds / 3600
set @minutes = (@seconds - (@hours * 3600)) / 60
set @seconds = (@seconds - (@hours * 3600) - (@minutes * 60))
if cast(@hours as int) < 10 set @hours = '0' + @hours
if cast(@minutes as int) < 10 set @minutes = '0' + @minutes
if cast(@seconds as int) < 10 set @seconds = '0' + @seconds
set @duration = @hours + ':' + @minutes + ':' + @seconds

Thanks Adriaan.
I had thought about limiting hours to less than 100 but I put this together to start tracking DBA jobs (backups, index builds etc) for a reporting setup I’m writing. Anything taking longer than 100 hours would be a real problem for a DBA job but I see your point also.
Thanks for the shorter code. Looks great.
Patrick