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
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