SQL Server - Round TIME values to the next minute

Question

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?

My code:

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

My result is the following:

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

I want BEGIN_TIME_ROUNDED = 16:00:00.0000000

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

Solution 1

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

EDIT

As pointed out in a comment this fails for times between 0 and 1 second. This can be combatted by simply changing the precision in the ceiling from seconds to milliseconds:

SELECT  PA.ORA_INIZIO,
        DATEADD(MINUTE, 
            CEILING(DATEDIFF(MILLISECOND, 0, CAST(PA.ORA_INIZIO AS TIME)) / 60000.0),
            DATEDIFF(DAY, 0, PA.ORA_INIZIO)) AS BEGIN_TIME_ROUNDED
FROM (VALUES 
        (CONVERT(DATETIME, '20211126 15:59:00.997')), 
        (CONVERT(DATETIME, '20211126 15:59:00.004'))
    ) AS PA (ORA_INIZIO);

Which gives:

ORA_INIZIO	BEGIN_TIME_ROUNDED
2021-11-26 15:59:59.997	2021-11-26 16:00:00.000
2021-11-26 15:59:00.003	2021-11-26 16:00:00.000

Solution 2

Just CAST to smalldatetime for rounding to nearest minute

SELECT 
    CAST(CAST('15:59:59.9970000' AS time) AS smalldatetime),
    CAST(CAST('15:59:30.0030000' AS time) AS smalldatetime),
    CAST(CAST('15:59:30.0000000' AS time) AS smalldatetime),
    CAST(CAST('15:59:29.9970000' AS time) AS smalldatetime),
    CAST(CAST('15:59:00.0030000' AS time) AS smalldatetime)

The DATEADD/DATEDIFF is for truncating some time unit

Edit, misread questions

Just modify your current CAST

CAST(
 DATEADD(minute,
         DATEDIFF(minute, 
                  0, 
                  CAST(PA.ORA_INIZIO AS DATETIME)
                 ) + 1, 
         0
         )
     AS TIME)

Solution 3

Don't know SQL Server well enough to answer off hand, but if no one comes by with a more more de facto way of doing this, then you could just add 1 minute to the value before rounding it down. Or add 0.999 minutes if you need to handle integer input values correctly as well.