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)'
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 */