[sql-server-2008] SQL Server - calculate elapsed time between two datetime stamps in HH:MM:SS format

I have a SQL Server table that has a "Time" column. The table is a log table the houses status messages and timestamps for each message. The log table is inserted into via a batch file. There is an ID column that groups rows together. Each time the batch file runs it initializes the ID and writes records. What I need to do is get the elapsed time from the first record in an ID set to the last record of the same ID set. I started toying with select Max(Time) - Min(Time) from logTable where id = but couldn't figure out how to format it correctly. I need it in HH:MM:SS.

This question is related to sql-server-2008 datetime elapsedtime

The answer is


UPDATED:

Correctly calculate a timespan in SQL Server, even if more than 24 hours:

-- Setup test data
declare @minDate datetime = '2012-12-12 20:16:47.160'
declare @maxDate datetime = '2012-12-13 15:10:12.050'

-- Get timespan in hh:mi:ss
select cast(
        (cast(cast(@maxDate as float) - cast(@minDate as float) as int) * 24) /* hours over 24 */
        + datepart(hh, @maxDate - @minDate) /* hours */
        as varchar(10))
    + ':' + right('0' + cast(datepart(mi, @maxDate - @minDate) as varchar(2)), 2) /* minutes */
    + ':' + right('0' + cast(datepart(ss, @maxDate - @minDate) as varchar(2)), 2) /* seconds */

-- Returns 18:53:24

Edge cases that show inaccuracy are especially welcome!


Use the DATEDIFF to return value in milliseconds, seconds, minutes, hours, ...

DATEDIFF(interval, date1, date2)

interval REQUIRED - The time/date part to return. Can be one of the following values:

year, yyyy, yy = Year
quarter, qq, q = Quarter
month, mm, m = month
dayofyear = Day of the year
day, dy, y = Day
week, ww, wk = Week
weekday, dw, w = Weekday
hour, hh = hour
minute, mi, n = Minute
second, ss, s = Second
millisecond, ms = Millisecond

date1, date2 REQUIRED - The two dates to calculate the difference between


select convert(varchar, Max(Time) - Min(Time) , 108) from logTable where id=...


Hope this helps you in getting the exact time between two time stamps

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

The best and simple way:

Convert(varchar, {EndTime} - {StartTime}, 108)

Just like Anri noted.


DECLARE @EndTime AS DATETIME, @StartTime AS DATETIME

SELECT @StartTime = '2013-03-08 08:00:00', @EndTime = '2013-03-08 08:30:00'

SELECT CAST(@EndTime - @StartTime AS TIME)

Result: 00:30:00.0000000

Format result as you see fit.


See if this helps. I can set variables for Elapsed Days, Hours, Minutes, Seconds. You can format this to your liking or include in a user defined function.

Note: Don't use DateDiff(hh,@Date1,@Date2). It is not reliable! It rounds in unpredictable ways

Given two dates... (Sample Dates: two days, three hours, 10 minutes, 30 seconds difference)

declare @Date1 datetime = '2013-03-08 08:00:00'
declare @Date2 datetime = '2013-03-10 11:10:30'
declare @Days decimal
declare @Hours decimal
declare @Minutes decimal
declare @Seconds decimal

select @Days = DATEDIFF(ss,@Date1,@Date2)/60/60/24 --Days
declare @RemainderDate as datetime = @Date2 - @Days
select @Hours = datediff(ss, @Date1, @RemainderDate)/60/60 --Hours
set @RemainderDate = @RemainderDate - (@Hours/24.0)
select @Minutes = datediff(ss, @Date1, @RemainderDate)/60 --Minutes
set @RemainderDate = @RemainderDate - (@Minutes/24.0/60)
select @Seconds = DATEDIFF(SS, @Date1, @RemainderDate)    
select @Days as ElapsedDays, @Hours as ElapsedHours, @Minutes as ElapsedMinutes, @Seconds as ElapsedSeconds