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

GarethD · Accepted Answer · 2021-11-26T12:15:42.997

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

This will not work when the time column is more than 0 second and less than 1 second. In this case the syntax will round down — t-clausen.dk, Nov 26 '21 at 12:05
@t-clausen.dk Thank you for the feedback. I have modified the answer to deal with this case. — GarethD, Nov 26 '21 at 12:18

gbn · Answer 2 · 2012-02-14T09:46:05.040

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)

edited Feb 14 '12 at 09:46

answered Feb 14 '12 at 09:38

gbn

422,506
82
585
676

2

-1 This does not answer the question at all. The OP wants to always round up to the nearest minute, the solution you have posted will round normally to the nearest minute, i.e. the last 2 columns of your select statement round down to 15:59 rather than up to 16:00. – GarethD Feb 14 '12 at 09:42
@GarethD: fixed it, I missed that. Thanks – gbn Feb 14 '12 at 09:46
there is a problem '20:30' will be rounded up to '20:31' – t-clausen.dk Feb 15 '12 at 08:45
it is a ceiling question. 20:30 should not be rounded up. At least not how i read the question. You are just rounding down and adding 1 minute – t-clausen.dk Jul 01 '15 at 09:48

score 1 · Answer 3 · answered Feb 14 '12 at 09:34

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.

score 0 · Answer 4 · answered Feb 14 '12 at 09:38

0

If you wanted to round DATETIME d up to the nearest minute, you could do this:

CONVERT(DATETIME, CONVERT(SMALLDATETIME,
  DATEADD(minute, CASE WHEN d = CONVERT(SMALLDATETIME, d) THEN 0 ELSE 1 END,
  d)))

answered Feb 14 '12 at 09:38

John Pick

5,562
31
31

t-clausen.dk · Answer 5 · 2021-11-26T12:01:25.337

0

DECLARE @ datetime = '2021-11-26 00:00:00.997'
SELECT dateadd(minute, ceiling(cast(@ as float) * 1440),0) ceilingminute

edited Nov 26 '21 at 12:01

answered Feb 14 '12 at 11:41

t-clausen.dk

43,517
12
59
92

SQL Server - Round TIME values to the next minute

5 Answers5

Linked