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

Navigation
3

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: 

RalentiTiempoTotal
    01:06:10

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
source

2 answers

6

MySQL

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

Example: 

SELECT TIME_TO_SEC(RalentiTiempoTotal) / 60

DEMO

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 .

Example: 

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

DEMO

    
answered by 16.03.2017 / 19:26
source
1

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
FROM 
(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
FROM 
(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
how to send an email that has a html and php code message? How to show records between two dates?