Menu
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)' 
395
Duration column's calculation like this. If minutes is your number of minutes, minutes%60 will give you the minutes only, (minutes/60)%24 will give you hours only, minutes/(60*24) will give you days only.
There are 60 minutes in 1 hour. To convert from minutes to hours, divide the number of minutes by 60. For example, 120 minutes equals 2 hours because 120/60=2.
How do I convert hh:mm:ss time values into total minutes in SQL? DataGrip, a powerful GUI tool for SQL. Smart code completion, on-the-fly analysis, quick-fixes, refactorings that work in SQL files, and more. SELECT EXTRACT (hour FROM t)*60+EXTRACT (minutes FROM t)+EXTRACT (seconds FROM t)/60; Pretty much the way you would anywhere else.
For those who need convert minutes to time with more than 24h format: DECLARE @minutes int = 7830 SELECT CAST (@minutes / 60 AS VARCHAR (8)) + ':' + FORMAT (@minutes % 60, 'D2') AS [Time] Thanks to A Ghazal, just what I needed.
Then write some simple SQL to do easy math. Seconds/60 = minutes (round as you desire). Minutes/60 - hours. Now all you have to do is sum up the hours. If you allowed minutes and seconds to have positions to the right of the decimal point, you now have decimal hours to as much precision as you choose.. Download the eBook Today!
#"Calculated Total Minutes" = Table.TransformColumns (Table.AddColumn (Source, "TotalMinutes", each Duration.FromText ( [Duration])), { {"TotalMinutes", Duration.TotalMinutes, type number}}) If you want to implement it via DAX, then you can create this measure. Please refer to the attachment below for details. Hope this helps.
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 */ If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
