CODEWITHSUNDEEP
t-sql
Dafydd Giddins
Dafydd Giddins

Reputation: 2316

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

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?

Upvotes: 5

Views: 39840

Answers (5)

JMat
JMat

Reputation: 23

In SQL Server 2012:

SELECT format(DATEADD(minute, 70, 0), N'hh\.mm')

Upvotes: -2

Ron H
Ron H

Reputation: 85

CAST(Duration/60 + (duration % 60) /100.0 as Decimal(10,1))

Upvotes: 0

idstam
idstam

Reputation: 2878

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

Upvotes: 1

lc.
lc.

Reputation: 116458

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.

Upvotes: 17

Eminem
Eminem

Reputation: 21

I spent a while trying to do the same thing, heres how i done it: 

convert( varchar, cast((RouteMileage / @average_speed) as integer))+ ':' +  convert( varchar, cast((((RouteMileage / @average_speed) - cast((RouteMileage / @average_speed) as integer)) * 60) as integer)) As TravelTime,

dateadd( n, -60 * CAST( (RouteMileage / @average_speed) AS DECIMAL(7,2)), @entry_date) As DepartureTime

OUTPUT:

DeliveryDate                TravelTime             DepartureTime
2012-06-02 12:00:00.000       25:49         2012-06-01 10:11:00.000

Upvotes: 0

Related Questions