SQL Server - 将 TIME 值舍入到下一分钟 [英] SQL Server - Round TIME values to the next minute

问题描述

我发现了很多关于舍入向下"时间值的帖子(例如 https://stackoverflow.com/a/6667041/468823)，但我还有另一个问题:我想四舍五入到更高的分钟而不是更低的分钟，我该怎么办?

I've found many posts about rounding "down" time values (e.g. https://stackoverflow.com/a/6667041/468823), but I have another problem: I wanna round to the higher minute and not to the lower, how can I do?

我的代码:

SELECT

 PA.ORE AS TOT_HOURS,  
 CAST(CAST(PA.ORA_INIZIO AS DATETIME) AS TIME) AS BEGIN_TIME,
 CAST(dateadd(minute, datediff(minute, 0, (CAST(PA.ORA_INIZIO AS DATETIME))), 0) AS TIME) AS BEGIN_TIME_ROUNDED


FROM PRG_ATTIVITA PA INNER JOIN PRG_TIPI_ATTIVITA PTA ON  PA.ID_TIPO_ATTIVITA = PTA.ID_TIPO_ATTIVITA
                     INNER JOIN PER_ANAGRAFICA PAN ON PA.ID_DIPENDENTE = PAN.ID_DIPENDENTE
WHERE PA.ID_PROGETTO = 1431 and pta.DESCR_TIPO_ATTIVITA like 'F-%remoto%' and ID_ATTIVITA = 41772 

ORDER BY  PA.DATA_ATTIVITA

我的结果如下:

    TOT_HOURS   BEGIN_TIME          BEGIN_TIME_ROUNDED
    1.50        15:59:59.9970000    15:59:00.0000000

我想要 BEGIN_TIME_ROUNDED = 16:00:00.0000000

注意:1. 我必须转换我的数据 { CAST(PA.ORA_INIZIO AS DATETIME) } 因为在数据库中我有时间数据作为浮点值2. BEGIN_TIME 是我转换后的时间价值的真实值

NOTES: 1. I must convert my data { CAST(PA.ORA_INIZIO AS DATETIME) } because in the database I have time data as float values 2. BEGIN_TIME is the real value of my time value after conversion

推荐答案

SELECT  DATEADD(MINUTE, CEILING(DATEDIFF(SECOND, 0, CAST(CAST(PA.ORA_INIZIO AS DATETIME) AS TIME)) / 60.0), DATEDIFF(DAY, 0, PA.ORA_INIZIO)) AS BEGIN_TIME_ROUNDED

这篇关于SQL Server - 将 TIME 值舍入到下一分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！