Round to .5 or 1.0 in SQL

Round to .5 or 1.0 in SQL

SELECT ROUND(2.2 * 2, 0) / 2 

gets you to the nearest .5

T-SQL Rounding to 0.0, 0.5, or 1.0

ORDER BY
    CASE WHEN (Num % 1) = .5
        THEN Num
        ELSE ROUND(Num,0)
    END

Round to nearest 5 in SQL Server

select round(FineAmount*2,-1)/2 from tickets

or to put nicholaides suggestion in sql

select round(FineAmount/5,0)*5 from tickets

The example assumes that FineAmount is of type money.
The second approach is probably better as the first one works with the limit of maximum_value_of_money_type/2

More on ROUND

Round to 0.5 in sql

val := round(val*2) / 2;

Such expresion should be what you described in question. So function is:

create or replace function func(val number) return number
is
begin 
  return round(val*2) / 2;
end;

But looking on examples you don't want closest number but highest number smaller than your value rounded to 0.5. And this you will obtain with:

create or replace function func(val number) return number
is
begin 
  return floor(val*2) / 2;
end;

If you would like the smallest number greater than value rounded to 0.5 it would be:

create or replace function func(val number) return number
is
begin 
  return ceil(val*2) / 2;
end;

how to round of decimal digit to nearest 50

I don't know if this is an efficient way or not but I tried this :

IF OBJECT_ID('MyTable','U') IS NOT NULL 
    DROP TABLE MyTable
GO
CREATE TABLE MyTable (num DECIMAL(10,2))
GO
INSERT INTO MyTable 
values (1.00),(1.01),(1.49),(1.50),(1.51),(1.99),(2.00),
(11.00),(11.01),(11.49),(11.50),(11.51),(11.99),(12.00)

SELECT [num],
       CASE
         WHEN ( [num] - [Nbr] ) BETWEEN 0.01 AND 0.49 THEN [Nbr] + 0.5
         ELSE [Nbr]
       END AS [Result]
FROM   (SELECT [num],
               ROUND(num, 0) AS [Nbr]
        FROM   MyTable) t 

Note: Case when condition can be modified as per requirement.

Oracle SQL - Round - Half

The documentation shows you the algorithm used:

1. If n is 0, then ROUND always returns 0 regardless of integer.
2. If n is negative, then ROUND(n, integer) returns -ROUND(-n, integer).
3. If n is positive, then
   
   ROUND(n, integer) = FLOOR(n * POWER(10, integer) + 0.5) * POWER(10, -integer)

So you could modify the positive, non-zero version:

FLOOR(n * POWER(10, integer) + 0.4) * POWER(10, -integer)
                                 ^

e.g. for a fixed rounding, and ignoring zeros/negative for now:

with t (my_number) as (
  select 3.674 from dual
  union all select 3.675 from dual
  union all select 3.676 from dual
)
select my_number,
  floor(my_number * power(10, 2) + 0.4) * power(10, -2) as round_on_number
from  t;

 MY_NUMBER ROUND_ON_NUMBER
---------- ---------------
     3.674            3.67
     3.675            3.67
     3.676            3.68

You could include zero/negative via a case expression; or write your own function to handle it more neatly.

SQL Converting to Decimal then Round to Nearest Half Number

Your approach seems very complicated. Why not just write the CASE using inequalities:

DECLARE @Time2 DECIMAL(10, 6);
SET @Time2 = CAST(@Time AS DECIMAL(10, 6));

SET @Number = (CASE WHEN @Time2 <= 00.50 THEN 0.016000
                    WHEN @Time2 <= 01.00 THEN 0.013300
                    WHEN @Time2 <= 01.50 THEN 0.012650
                    . . .
               END);

Rounding UP in SQL Server?

Did you try Casting either the numerator and the denominator as float and then using Cieling?

Integer operations always give integers. Try the following -

SELECT @TotalPages = CEILING((SELECT cast(COUNT(*) as float) FROM #TempItems) / @RecsPerPage ) 

---

Related Topics