Home / Questions / 418913
Accepted
Javier fr
Asked: 2022-01-07 10:58:16 +0800 CST 2022-01-07 10:58:16 +0800 CST

Round number from 8.6 to 8.5 using SQL SERVER

  • 772

Hello good afternoon to everyone.

I'm having a problem when trying to round grades using SQL SERVER.

I attach some examples:

I need to round 8.6 to 8.5 (from 8.9 to 8.5 it stays at 8.5)

Try

SELECT ROUND(8.6, 1, 1) AS resultado;

Result (Not rounding me)

8.6

Another example is rounding from 8.1 to 8.0 (from 8.4 to 8.0 it stays at 8.0)

Try

SELECT ROUND(8.1, 1, 1) AS resultado;

Result (Not rounding me)

8.1

I hope you can support me.

Greetings and have a great Wednesday.

sql-server

2 Answers

  1. Best Answer

    I don't think there is a function that does EXACTLY what you ask for. At least not a "built in Function". But...

    If you want to get creative, you can do something like this:

    1. The function parsename(Calificacion,1)returns the right part of the number, that is, the decimal part. This is what we want to evaluate, right?
    2. We do a conditional where, according to your parameters, if the decimal part is between five and nine (including them), then the integer part of the number and '.5' are concatenated. If if the decimal part is between zero and four(inclusive), then the integer part of the number and '.0' are concatenated

    You can run this code in your SSMS and it will work on sqlServer 2016 and newer.

         Drop table if exists #numbers
     CREATE TABLE #Numbers (Calificacion DECIMAL(16, 1));
     INSERT INTO #Numbers (Calificacion)
     VALUES (8.6);
     INSERT INTO #Numbers (Calificacion)
     VALUES( 8.4);
     INSERT INTO #Numbers (Calificacion)
     VALUES (390.7);
     INSERT INTO #Numbers (Calificacion)
     VALUES (12.7);
     INSERT INTO #Numbers (Calificacion)
     VALUES (390.1);




     SELECT     Calificacion
                    , PARSENAME(Calificacion,1) AS ParteDecimal             
                    ,Case 
                          when PARSENAME(Calificacion,1)>=5 AND PARSENAME(Calificacion,1)<=9
                          Then Concat(Parsename(Calificacion,2),'.5')   
                          When PARSENAME(Calificacion,1)>=0 AND PARSENAME(Calificacion,1)<=4
                          then Concat(Parsename(Calificacion,2),'.0')
                         end as CalificacionRedondeada
 
 FROM #NUMBERS

    Result:

    Calificacion ParteDecimal CalificacionRedondeada
-------------------------------------------------
8.6      |       6          |       8.5
8.4      |       4          |       8.0
390.7    |       7          |       390.5
12.7     |       7          |       12.5
390.1    |       1          |       390.0
    • 2

  2. If you need to round to whole values ​​or halves, it's easiest to multiply the number by two, truncate it, and divide it by two again. You can truncate with the third parameter of ROUND()or using FLOOR()but the functionality changes on negative values.

     CREATE TABLE #Numbers (Calificacion DECIMAL(16, 1));
 INSERT INTO #Numbers (Calificacion)
 VALUES (8.6),
        ( 8.4),
        (390.7),
        (12.7),
        (390.1),
        (-3.4);

 SELECT Calificacion,
        CAST( ROUND( Calificacion*2,0,1)/2 AS DECIMAL(16, 1)),
        CAST( FLOOR( Calificacion*2)/2 AS DECIMAL(16, 1))
 FROM #Numbers;

 GO
 DROP TABLE #numbers;
    • 0