SQL: Is there a more efficient way to calculate elapsed hours, minutes, seconds?

I'm using Computed Columns to provide me with the following information:

Hours, Minutes, and Seconds between a Start DateTime and Finish DateTime (Where Minutes and Seconds are between 0 and 59 and Hours can be any value 0 or greater)

Compute开发者_JAVA百科d Column for Seconds:

datediff(second,[Start],[Finish]) % 60

Computed Column for Minutes:

floor(datediff(second,[Start],[Finish]) / 60.0) % 60

Computed Column for Hours:

floor(datediff(second,[Start],[Finish]) / 3600.0)

Here's the table for reference

Note: I'm also calculating TotalElapsedSeconds, TotalElapsedMinutes, and TotalElapsedHours in other computed columns, but those are easy. I just feel like I might be missing out on a nice built in function in SQL.

Note: I have a check constraint that ensures that Finish > Start

In terms of finding the hours, minutes, and seconds between two dates similar to the functions you are using, you could use DatePart like so:

DatePart(hh, Finish - Start)
DatePart(mi, Finish - Start)
DatePart(s, Finish - Start)

if you are using sql server, you can use

datediff(minute,[Start],[Finish])
datediff(hour,[Start],[Finish])

Does your dialect of SQL support this syntax for date interval math:

db=> select to_char(timestamp '2010-05-21 10:10:10' - '2009-10-11', 'ddd hh24:mi:ss');
   to_char    
--------------
 222 10:10:10
(1 row)

db=> select to_char(timestamp '2010-05-21 10:10:10' - '2001-10-11', 'ddd hh24:mi:ss');
    to_char    
---------------
 3144 10:10:10
(1 row)

db=> select to_char(timestamp '2010-05-21 10:10:10' - '2010-05-21', 'ddd hh24:mi:ss');
   to_char    
--------------
 000 10:10:10
(1 row)

Create PROC TimeDurationbetween2times(@iTime as time,@oTime as time) 
As  
Begin  

DECLARE @Dh int, @Dm int, @Ds int ,@Im int, @Om int, @Is int,@Os int     

SET @Im=DATEPART(MI,@iTime)  
SET @Om=DATEPART(MI,@oTime)  
SET @Is=DATEPART(SS,@iTime)  
SET @Os=DATEPART(SS,@oTime)  

SET @Dh=DATEDIFF(hh,@iTime,@oTime)  
SET @Dm = DATEDIFF(mi,@iTime,@oTime)  
SET @Ds = DATEDIFF(ss,@iTime,@oTime)  

DECLARE @HH as int, @MI as int, @SS as int  

if(@Im>@Om)  
begin  
SET @Dh=@Dh-1  
end  
if(@Is>@Os)  
begin  
SET @Dm=@Dm-1  
end  

SET @HH = @Dh  
SET @MI = @Dm-(60*@HH)  
SET @SS = @Ds-(60*@Dm)  

DECLARE @hrsWkd as varchar(8)         

SET @hrsWkd = cast(@HH as char(2))+':'+cast(@MI as char(2))+':'+cast(@SS as char(2))          

select @hrsWkd as TimeDuration   

End