How to round off time?

Question

How to round off time?

Navigation

#1 by (9 votes)

6

I have the following function that treats a time that comes 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

It returns:

 03:56:59
 00:43:12

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

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

sql sql-server query

asked by anonymous 10.02.2017 / 14:47

1 answer

How does initializing fields in constructors work? How to remove spaces leaving only one?

score 9 · Accepted Answer

Using the DATEDIFF function you get an integer representing the difference between two dates. Since the rounding you want is in minutes, you only need to calculate the difference between the 0 and the specified time plus 30 seconds, which will "jump" one minute if you have 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 moment zero, thus obtaining the minute rounded:

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

To show the formatted time use CONVERT :

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

Applying to your examples

03:56:59 shows 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 shows 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 (signed integer) of the specified limits of datepart crossed between the specified parameters startdate and enddate .

DATEADD

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

datepart and CAST

Converts an expression from one data type to another.

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