DateDiff() Hours and Minutes?

Question

This question have been asked many times but i cannot find any easy answers on how to get hours and minutes from a datediff().

From    To  OUTPUT
08:00   16:30   8,5
10:00   16:30   6,5
08:00   15:00   7

I would like to use datediff() and want the output as my result

Solution 1

I went with

DATEDIFF(second, start_date, end_date) / 3600.0          

Thank you all for the answers...!!.

Solution 2

Sorry, I answered C# question first :)

What you're after is this:

SELECT datediff(minute, starttime, endtime) from ...

of course, you can apply this to hours too. For Hours AND minutes, you can use this example:

DECLARE @start datetime
      , @end   datetime

SELECT @start = '2009-01-01'
     , @end   = DateAdd(mi, 52, DateAdd(hh, 18, DateAdd(dd, 2, @start)))

SELECT @start
 , @end

SELECT DateDiff(dd, @start, @end)      As days
     , DateDiff(hh, @start, @end) % 24 As hours
     , DateDiff(mi, @start, @end) % 60 As mins

Solution 3

just take datediff() by minute and then divide by 60

select  datediff(minute, '08:00', '16:30') / 60.0

Comments

[Nils]

Hi, i do not want them as 2 or 3 different columns. can you provide the output column for me?

[Pedro G. Dias]

Just replace comma with + ' '

[Squirrel]

that will result in arimatic addition. you need to convert to string first before using +