Convert "hh:mm:ss" to int minutes

Question

I have the following function that calculates the hours 01:00, returns 60 minutes. but the column of sql store in time(0 format) 01:00:00, What error in my function, I do not know how to increment the code to calculate the seconds, and return me the entire 60 minutes.

CREATE FUNCTION [dbo].[FN_CONVHORA] (@Horas varchar(10))
RETURNS int
BEGIN
DECLARE @iMinutos INTEGER 
Select @iMinutos =
  (Convert(int, SubString(Convert(VarChar(10), @Horas), 1, 
  CharIndex(':', Convert(VarChar(10), @Horas)) - 1)) * 60) + (Convert(int, SubString(Convert(VarChar(10), @Horas), 
  CharIndex(':', Convert(VarChar(10), @Horas)) + 1, 
  Len(Convert(VarChar(10), @Horas)) -  CharIndex(':', Convert(VarChar(10), @Horas)))))
 RETURN @iMinutos
END
GO

Answer 1

Assuming you are using a date type to store your data, you don’t even need a function for it, just use the DATEDIFF

SELECT DATEDIFF(second, 0, '20:10:10') AS diferenca_em_segundos

Example in SQL Fiddle

Syntax of DATEDIFF

DATEDIFF ( datepart , startdate , enddate )

datepart - part of the date to be returned, in your case cond.

startdate - start date. Since you want to count seconds only from time, consider zero.

enddate - the final date of the interval. In your case the desired time.

Answer 2

Replace your function with this call to return the hours in minutes:

SELECT CAST(LTRIM(DATEDIFF(MINUTE, 0, '01:03:00')) AS INT)

If you want to get the information directly from a table, you can do it as follows:

SELECT CAST(LTRIM(DATEDIFF(MINUTE, 0, NomeDaColuna)) AS INT) FROM NomeTabela

You can adapt your function with this code instruction as well:

CREATE FUNCTION FN_CONVHORA(@Horas VARCHAR(10))
RETURNS INT
BEGIN
    RETURN (SELECT CAST(LTRIM(DATEDIFF(MINUTE, 0,convert(varchar(10), @Horas, 108))) AS INT))
END

Good luck!