CODEWITHSUNDEEP
t-sql
user1240661
user1240661

Reputation: 81

Convert HH:MM:SS string to number of minutes

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.

Upvotes: 4

Views: 28002

Answers (8)

Junior Grão
Junior Grão

Reputation: 1661

Unfortunately, if you want to use DATEPART function for values with more than 24 hours, you will receive an error:

Conversion failed when converting date and/or time from character string."

You can test it with this code:

declare @Time DATETIME = '32:00:00'
select ((DATEPART(HOUR, @Time)*60) + (DATEPART(MINUTE, @Time)))

To solve this, I worked with this another approach:

declare @tbl table(WorkHrs VARCHAR(8))
insert into @tbl(WorkHrs) values ('02:47:00')
insert into @tbl(WorkHrs) values ('32:00:00')
-- Sum in minutes
SELECT TRY_CAST(([HOURS] * 60) + [MINUTES] + ([SECOND] / 60) AS INT) as TotalInMinutes
FROM (
SELECT 
    -- Use this aproach to get separated values 
    SUBSTRING(WorkHrs,1,CHARINDEX(':',WorkHrs)-1) AS [HOURS],
    SUBSTRING(WorkHrs,4,CHARINDEX(':',WorkHrs)-1) AS [MINUTES],
    SUBSTRING(WorkHrs,7,CHARINDEX(':',WorkHrs)-1) AS [SECOND] -- probably you can ignore this one
    FROM @tbl
)
tbl
-- Sum in seconds
    SELECT TRY_CAST(([HOURS] * 3600) + ([MINUTES] * 60) + [SECOND] AS INT) as TotalInSeconds
    FROM (
    SELECT 
    -- Use this aproach to get separated values 
    SUBSTRING(WorkHrs,1,CHARINDEX(':',WorkHrs)-1) AS [HOURS],
    SUBSTRING(WorkHrs,4,CHARINDEX(':',WorkHrs)-1) AS [MINUTES],
    SUBSTRING(WorkHrs,7,CHARINDEX(':',WorkHrs)-1) AS [SECOND]
    FROM @tbl
)
tbl

This code will return like this:

enter image description here

Upvotes: 0

Zero Cool
Zero Cool

Reputation: 144

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

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

Upvotes: 4

Shane
Shane

Reputation: 159

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

Upvotes: 2

Brabazoo
Brabazoo

Reputation: 21

$time = '02:47:00';

$time = explode(":",$time); 

$total = ($a[0]*60)+$a[1];

echo 'Minutes : '.$total;<br>
echo 'Seconds : '.$a[2];

Upvotes: -1

peterm
peterm

Reputation: 92785

SELECT DATEDIFF(minute,CAST('00:00' AS TIME), CAST('02:47' AS TIME)) AS difference

Gives you:

| DIFFERENCE |
--------------
|        167 |

Upvotes: 0

Justin
Justin

Reputation: 9724

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 |

Upvotes: 6

Andrey Gordeev
Andrey Gordeev

Reputation: 32449

For SQL Server (works for 2005 too):

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

Upvotes: 4

t-clausen.dk
t-clausen.dk

Reputation: 44316

Try this:

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

Upvotes: 3

Related Questions