Geek Answers Handbook

Format minutes in hours and minutes as decimal in T-SQL

Is there a simple and easy way to format an integer, which is the number of whole several minutes in decimal representation of hours and minutes. It’s unfortunate that there is no such thing as Timespan in T-SQL.

Just to understand what I mean, if I have 70 minutes, I want to hide it in the form of 1 hour and 10 minutes, i.e. 1.10. I also want to keep it as varchar or something to ensure that the trailing zero is kept in place.

In any case, could this be created as a user-defined function in SQL so that it can be reused from different queries?

+5
source share
5 answers

, 60. , 60, 60. , 100.0:

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

, , , , , : ) b) - , .

, ( ':' '.' , ):

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

, , . , .

, , , TimeSpan.

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

I spent some time trying to do the same, here is how I did 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
0
source 
CAST(Duration/60 + (duration % 60) /100.0 as Decimal(10,1))
0
source

In SQL Server 2012:

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

More articles:


All Articles