t-sql

Round to the closest .25

Does anyone know of a way to round to the closest .25 in t-sql? Currently I am rounding down using 

floor(value * 4)/4

My client is changing their algorithm and wants to do a midpoint round to the closest quarter. If the value is less than .125 round to 0.00, if the value is greater than or equal to .125 round up to .25.

Solution

  • use ROUND(value/25, 2) * 25 like this:

    Example1:

    DECLARE @value DECIMAL(18, 2)
SET @value = 1.126
SELECT CAST(ROUND(@value/25, 2) * 25 as numeric(18,2)) AS rounded_val

    Output:

    1.25

    Example2:

    DECLARE @value DECIMAL(18, 2)
SET @value = 1.124
SELECT CAST(ROUND(@value/25, 2) * 25 as numeric(18,2)) AS rounded_val

    Output:

    1.00