How to convert number of minutes to hh:mm format in TSQL?

datediff, sql-server, sql-server-2008, t-sql, type-conversion

Solution

You can convert the duration to a date and then format it:

DECLARE
    @FirstDate datetime,
    @LastDate datetime

SELECT
    @FirstDate = '2000-01-01 09:00:00',
    @LastDate = '2000-01-01 11:30:00'

SELECT CONVERT(varchar(12), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30:00:000 */

For less precision, modify the size of the varchar:

SELECT CONVERT(varchar(5), 
       DATEADD(minute, DATEDIFF(minute, @FirstDate, @LastDate), 0), 114) 

/* Results: 02:30 */

Problem

I have a select query that has `DURATION` column to calculate number of Minutes . I want to convert those minutes to `hh:mm` format. Duration has values like `60, 120,150` For example: 60 becomes 01:00 hours 120 becomes 02:00 hours 150 becomes 02:30 hours Also, this is how I retrieve DURATION (`Minutes`) ``` DATEDIFF(minute, FirstDate,LastDate) as 'Duration (Minutes)' ```

Original source