Format minutes into hours and minutes as a decimal number in T-SQL

Question

Is there a clean and simple method of formatting an integer which is a count of a whole number of minutes into a decimal representation of hours and minutes. It's a great shame that there is not such thing as a Timespan in T-SQL to support this.

Just to be clear what I mean by this is if I have 70 minutes, I want to covert it into a representation of 1 hour and 10 minutes i.e. 1.10. I'll also want to keep it as a varchar or something to ensure that the trailing zero is kept in place.

Is there anyway that this could be created as a custom function in SQL so that it can be resused from different queries?

Answer 1

To get the hours, you want to divide by 60. To get the remaining minutes, you want to either take the total minutes mod 60 or the difference between the total minutes and hours times 60. To make the remainder appear to the right of the decimal place, divide by 100.0:

SELECT minutes / 60 + (minutes % 60) / 100.0 ...

Note that you'll lose the trailing zero and IMHO storing a time duration this way is a bad idea because a)you can't do any more math with it and b)someone's going to get confused when they look at this code later.

You're much better off to use a string representation like the following (and I recommend the use of ':' instead of '.' again, to signify this is not a number):

SELECT CAST((minutes / 60) AS VARCHAR(8)) + ':' + 
       RIGHT('0' + CAST((minutes % 60) AS VARCHAR(2)), 2) ...

---

As far as creating a custom function, you can make a stored procedure and then call it from other code. Note that the stored procedure will have to be created in each database.

Not sure making the stored procedure is worth the effort though, unless you want to be fancier and return a TimeSpan-like row.

Answer 2

select 70/60 + ( 70 % 60)/100.0