Search code examples
sql-servert-sqltimehourminute

DATEDIFF - Display result in Hours and minutes

I am using the below to determine the minute between two times,

DateDiff(Minute, [MondayOpenTime] , [MondayCloseTime])

For a start time of 09:00 and a Close time of 17:30 it returns the value 510

Or if i use:

DateDiff(hour, [MondayOpenTime] , [MondayCloseTime])

It returns 8,

How do i get it to return the hours and minutes like 08:30

Using the minute, and then divide by 60, gives 8.5,

Note. I have been through many of the similar questions to this, if there is one with an exact answer i will delete this, but i cannot find any answers as of yet

Solution

  • You can calculate the hours by division as you stated. You can then use modulo to calculate the minutes.

    declare @MondayOpenTime time = '09:00'
    , @MondayCloseTime time = '17:30'

select convert(varchar(2), DateDiff(Minute, @MondayOpenTime , @MondayCloseTime) / 60) + ':' + right('0' + convert(varchar(2), DateDiff(Minute, @MondayOpenTime , @MondayCloseTime) % 60), 2)