Convert HH:MM:SS string to number of minutes

Question

I have the below query.

select cast(dateadd(minute, datediff(minute, TimeIn, TimeOut), 0) as time(0) )

I get the results from two columns in the format of hrs-min-seconds. I would like it in the format of min only. So 02:47:00 will read 167.

Solution 1

For SQL Server (works for 2005 too):

select Datediff(mi,convert(datetime,'00:00:00',108), convert(datetime,'02:47:00',108))

Solution 2

SQL Server Query:

SELECT cast(substring('02:47:00',1,2) AS int)*60+
       cast(substring('02:47:00',4,2) AS int)+ 
       cast(substring('02:47:00',7,2) AS int)/60.0 AS minutes

MYSQL Query:

SELECT TIME_TO_SEC('02:47:00') / 60

Result:

| MINUTES |
-----------
|     167 |

Solution 3

declare @Time DATETIME = '01:05:00'

select ((DATEPART(HOUR, @Time)*60) + (DATEPART(MINUTE, @Time)))

Solution 4

Try this:

datediff(minute, 0, '02:47')

Solution 5

Expanding on Justin's answer. This allows for situations where hours is larger than 2 digits.

declare @time varchar(50) = '102:47:05'

SELECT cast(right(@time,2) AS int)+
       cast(left(right(@time,5),2) AS int)*60+ 
       cast(left(@time,len(@time)-6) AS int)*3600 AS seconds,
       (cast(right(@time,2) AS int)+
       cast(left(right(@time,5),2) AS int)*60+ 
       cast(left(@time,len(@time)-6) AS int)*3600)/60.0 AS minutes

Result:

seconds     minutes
----------- ---------------------------------------
370025      6167.083333

View more solutions