How to round up time?

Question

I have the following function which treats an hour coming from the bank:

SELECT f.chapa AS chapa,
       f.nome AS nome,
       f.secao AS cod_secao,
       f.nsecao AS desc_secao,
       c.codigo AS cod_funcao,
       c.nome AS desc_funcao,
       f.situacao AS sistuacao,
       CONVERT(NVARCHAR, CONVERT(DATETIME, p.ref / 24), 108) AS horas,
       p.valor AS valor
  FROM pffinanc p
       LEFT JOIN vwfunc f ON f.chapa = p.chapa
       LEFT JOIN pfuncao c ON c.codigo = f.funcao
 WHERE p.codcoligada = 1
   AND p.nroperiodo = 3
   AND p.anocomp = 2017
   AND p.mescomp = 1
   AND p.codevento IN ('156', '185', '172', '249')
   AND p.chapa = 1234 

He returns:

 03:56:59
 00:43:12

What I need is to return only the hours and minutes leasing as follows:

• If seconds for > 30 : 03:57 (ex: 03:56:59)
• If seconds for < 30 : 00:43 (ex: 00:43:12)

Answer 1

Using the function DATEDIFF You get an integer representing the difference between two dates. As the rounding you want is in the minute box, you only need to calculate the difference between the moment 0 and the specified time summed 30 seconds, which will "skip" one minute if it has already passed more than 30 seconds of the minute in question:

DECLARE @diferenca INT = DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30, @tempo));

After this it is necessary to add the result obtained to the zero moment, thus obtaining the rounded minute:

SET @tempo = DATEADD(MINUTE, @diferenca, 0);

To show formatted time use CONVERT:

PRINT CONVERT(VARCHAR(5), @tempo, 108);

---

Applying to your examples

03:56:59 showcase 03:57:

DECLARE @tempo TIME = '03:56:59';
DECLARE @diferenca INT = DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30, @tempo));

SET @tempo = DATEADD(MINUTE, @diferenca, 0);

PRINT CONVERT(VARCHAR(5), @tempo, 108);

00:43:12 showcase 00:43:

DECLARE @tempo TIME = '00:43:12';
DECLARE @diferenca INT = DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30, @tempo));

SET @tempo = DATEADD(MINUTE, @diferenca, 0);

PRINT CONVERT(VARCHAR(5), @tempo, 108);

Simplifying:

SELECT CONVERT(varchar(5), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30, '03:56:59')), 0), 108),
       CONVERT(varchar(5), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DATEADD(SECOND, 30, '00:43:12')), 0), 108)

Example in SQL Fiddler

---

DATEDIFF

Returns the count (signal integer) of the specified limits of datepart cross-checked between the specified parameters startdate and enddate.

---

DATEADD

Returns a specified date with the specified number range (signed integer) added to the datepart specified at that date.

---

CAST and CONVERT

Converts an expression from one data type to another.

---

Adapted from the answer to the question T-SQL datetime rounded to Nearest minute and Nearest hours with using functions of Stack Overflow