how to get hours from subtracting two time values in SQL Server 2008

Question

I have two date values: 10:30:00 and 15:59:00.

How do I get the difference in hours and minutes in a query in SQL Server 2008? to give 05:29

and convert it to decimal to give 5.29

Answer 1

I would get the minutes and then handle the rest using arithmetic:

select (datediff(minute, val1, val2) / 60) +
       (datediff(minute, val1, val2) % 60 / 100.0)

Answer 2

try this

declare @date1 datetime
declare @date2 datetime

select @date1 = '00:25:10';
select @date2 = '23:05:10';

select cast(DATEDIFF(minute, @date1, @date2) / 60.0 as decimal(10,2))

Answer 3

CREATE Function [dbo].[udf_Elapsed] (@Date1 DateTime,@Date2 DateTime)

Returns Varchar(150)
As

Begin
    Declare @sec int,@hour int,@minut int,@days int 
    SET @sec = DATEDIFF (SECOND,@Date1,@Date2)
    Set @days = @sec / 86400
    Set @sec = @sec - (@days * 86400)
    Set @hour = @sec / 3600
    Set @sec = @sec - (@hour * 3600)
    Set @minut = @sec / 60
    Set @sec = @sec - (@minut * 60)
    Return right(concat('000',@days),3)+':'+right(concat('00',@hour),2)+':'+right(concat('00',@minut),2)+':'+right(concat('00',@sec),2)
End


Select [dbo].[udf_Elapsed] ('2016-05-05 10:30','2016-05-05 15:59')

returns 000:05:29:00 (days:hh:mm:ss)

Answer 4

Use DateDiff function like this to get result.