3

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

Mark
  • 2,041
  • 2
  • 18
  • 35
Nils
  • 516
  • 3
  • 9
  • 33
  • How is your data stored? – sagi Mar 07 '16 at 09:41
  • i want datediff(hours&minutes,from,to) as OUTPUT – Nils Mar 07 '16 at 09:41
  • How are from and to stored? – sagi Mar 07 '16 at 09:41
  • The datepart passed to DATEDIFF will control the resolution of the output. example, if start_date and end_date differed by 59 seconds, then DATEDIFF(MINUTE, start_date, end_date) / 60.0 would return 0, but DATEDIFF(second, start_date, end_date) / 3600.0 . – Chanukya Mar 07 '16 at 09:47

3 Answers3

3

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
Pedro G. Dias
  • 3,162
  • 1
  • 18
  • 30
2

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

select  datediff(minute, '08:00', '16:30') / 60.0
Squirrel
  • 23,507
  • 4
  • 34
  • 32
1

I went with 

DATEDIFF(second, start_date, end_date) / 3600.0

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

Nils
  • 516
  • 3
  • 9
  • 33