Home c# MS SQL rounding to the nearest black

# MS SQL rounding to the nearest black

Author

Date

Category

I have logic in the application on C #, which I was forced to start duplicate on SLQ Server in representations and stored and faced the fact that the calculation occurs in a different way.

Visual example. Some entity with a field of type Decimal, Code First, sign on SQL Server, type also Decimal. In C # math. Round In the case of half a half rounds the data to the nearest ball number (midpointrounding.toeven):

``````decimal val1 = 766.5m;
Decimal val2 = 767.5m;
Console.Writeline (Math.round (766.5M)); // 766.
Console.WriteLine (Math.round (767.5m)); // 768.
``````

At the same time Round SQL does not support such settings:

``````declare @ val1 decimal = 766.5;
Declare @ Val2 decimal = 767.5;
SELECT ROUND (@ VAL1, 0); - 767.
SELECT ROUND (@ VAL2, 0); - 768.
``````

How to make the round behavior in MS SQL the same as in C #?

PS Server version: MSSQL2016 (13.0.1601.5), .NET CORE 2.1

If I correctly understood the task, then you can:

``````declare @t table (COL Decimal (18,2))
INSERT @T (col)
Values ​​(-122), (-122.5),
(-123), (-123.5),
(-124), (-124.5),
(122), (122.5),
(123), (123.5),
(124), (124.5)
SELECT COL.
, IIF (CEILING (COL)% 2 = 0, CEILING (COL), FLOOR (COL))
From @t.
``````

Ceiling – returns the smallest integer, more or equal to the numerical expression transferred to it;

Floor – returns the greatest integer smaller or equal to the numerical expression transferred to it.

In the expression `Ceiling (COL)% 2 = 0 `we check the number with a fractional part of the fractional part (the residue from division by 2 should be zero). If the number is onely, then we take the least integer, otherwise the greatest integer.

IIF design can also be replaced with CASE for SQL versions less than 2012, which do not support iif:

``````Case Ceiling (Col)% 2 WHEN 0 THEN CEILING (COL) ELSE FLOOR (COL) END
``````

p.s. Stuff this design to the function I would not. In general, in SQL Server, the functions are better to stay away without extreme need, as they can be pitched stones in terms of query performance.

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.