Convert hours "hh: mm: ss" to minutes in int format

Question

Convert hours "hh: mm: ss" to minutes in int format

Navigation

#1 by (3 votes)
#2 by (0 votes)

1

I have the following function that calculates the 01:00 hours, returns 60 minutes. but the sql column stores in time (0) 01:00:00 format, What about the error in my function, I do not know how to increment the code to calculate the seconds, and return 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

sql-server tsql

asked by anonymous 28.03.2016 / 05:28

2 answers

Query id most often repeated Validating select with jquery.validate

score 3 · Answer 1

Assuming you're using a date type to store your data, you do not even need a function for it, just use the DATEDIFF

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

SQL Fiddle Example

Syntax of DATEDIFF

DATEDIFF ( datepart , startdate , enddate )

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

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

enddate - the end date of the range. In your case the desired time.

score 0 · 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 have the intention of getting 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 statement too:

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!