Search code examples
sqlsql-serverdate-arithmetic

SQL Server Round hours

I need to rounding time near 30 minutes.

This is an example:

1 minute -> 0,02 hours -> 0,5 hours

35 minutes -> 0.8 hours -> 1 hour

Solution

  • Use CEILING() (or CEIL(), for other DBMS) to round up:

    SELECT CEILING(1  / 30.0)*30   --> 30 minutes
SELECT CEILING(25 / 30.0)*30  --> 30 minutes
SELECT CEILING(35 / 30.0)*30  --> 60 minutes

    or if you prefer hours:

    SELECT CEILING(1  / 30.0)*30 / 60 --> 0.5 hour
SELECT CEILING(25 / 30.0)*30 / 60 --> 0.5 hour
SELECT CEILING(35 / 30.0)*30 / 60 --> 1 hour