How to convert a data of type string, to convert it into minutes? Using SQL Server and MySQL


I want to convert a data of type varchar to int, then I want to convert the result to minutes to get an average from there. This is the field:


What I am doing first is separating the fields in this way:

select sum (convert(float, substring(RalentiTiempoTotal,1,2))/60 + 
       convert(int, substring(RalentiTiempoTotal,4,2))/3600)
       from Tabladatos

In the first select what I do, I'm converting hours to minutes and then minutes to seconds. But it does not give me the result I want.

I know that in MySQL, there is a function (TIME_TO_SEC), which is used to calculate the minutes directly. What would be the equivalent to SQL Server?.

Can someone help me please? Thanks.

asked by Ric_hc 16.03.2017 в 19:15

2 answers



You could fix it using TIME_TO_SEC and the result you divide by 60


SELECT TIME_TO_SEC(RalentiTiempoTotal) / 60


SQL Server

Using CAST to convert time to datetime , then DATEDIFF to get timestamp in seconds, multiply by 1.0 to convert int to float and then divide by 60 .


SELECT total_minutos = DATEDIFF(second,0,CAST(@time as datetime)) * 1.0 / 60


answered by 16.03.2017 / 19:26

Assuming the following scenario

SELECT hora, 
       1*LEFT(hora,2) as horas, 
       1*RIGHT(LEFT(hora,5),2) as minutos, 
       1*right(hora,2) as segundos
(SELECT '00:06:10' as hora) t1

The result would be:

    hora        horas   minutos segundos
    00:06:10    0       6       10

If you want to take everything to minutes the total is calculated as

   horas * 60 + minutos + segundos/60

What in this case would be

SELECT hora, 
       1*LEFT(hora,2)*60 + 
       1*RIGHT(LEFT(hora,5),2) +
       1*right(hora,2)/60 as minutos
(SELECT '00:06:10' as hora) t1

And the result of that is 6.16666666 minutes

Fiddle: link

answered by 16.03.2017 в 19:25