tags:

views:

2041

answers:

2

Is there a clean and simple method of formatting an integer which is a count of a whole number of minutes into a decimal represnetation 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 1hour and 10 minutes i.e. 1.10. (i'll also want to keep it as a varchar or something to ensure that the trailng 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?

+3  A: 

Apparently I was missing something.

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)) + ':' + 
       CAST((minutes % 60) AS VARCHAR(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.

lc
+1, you're not missing anything :)
Patrick McDonald
Just remember that if you are going to divide an integer and hope to get decimals back, you should put the result into the appropriate data type.
TheTXI
@TheTXI good point
lc
I don't think you get what i mean. If i have 70 minutes, i want to covert it into a representation of 1hour and 10 minutes i.e. 1:10. I'll reword my question to make this clear
-1 I'm afraid this is not what he's looking for. idstam has the right answer.
John M Gant
@daffers: You're right, I didn't understand the first time around. Like I said, I must have been missing something.
lc
@jmgant: Yeah, I fixed my answer now that I understand what the question was looking for. Check the revision of the OP when I first posted this answer and tell me you don't read it the same way tho... :)
lc
+1  A: 

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

idstam