CODEWITHSUNDEEP
sqlsql-server
Blaze Tama
Blaze Tama

Reputation: 10948

SQL Server custom rounding

I have a situation where :

I tried Ceiling, but the result will be 25, where Floor will be 24 for all of them.

How to accomplish this?

Thanks a lot for your time.

Note: it might be helpful to let you know that I want this functionality to be inside a computed column.

Upvotes: 0

Views: 770

Answers (6)

APH
APH

Reputation: 4154

Use:

Round(YourNumber, 0)

The 0 indicates the precision (i.e. number of decimal places); if you wanted to round 42.51 to 42.5, you'd replace the 0 with 1, for example.

Make sure not to use floats - they can sometimes be approximated, which causes values to be rounded incorrectly on occasion.

Upvotes: 3

mohan111
mohan111

Reputation: 8865

you can proceed like this for your provide data 

select CAST(CEILING(24.5000)AS INT)
select CAST(CEILING(24.9999)AS INT)
select CAST(CEILING(24.1111 -1)AS INT)
select CAST(CEILING(24.4999 - 1)AS INT)

Or you have lot of data in you table with certain conditions then you can pick the decimal values after '.' dot and write case condition accordind to that it is not way to do so but to achieve results 

declare @t table (ID decimal(18,4))
insert into @t(ID)values (24.5000),(24.9999),(24.1111),(24.4999)
select case when RIGHT(ID, LEN(ID) - CHARINDEX ('.', ID)) - 1 > 4500
THEN CAST(CEILING(ID)AS INT)  
ELSE CAST(CEILING(ID - 1)AS INT) END from @t

Upvotes: 0

Rashedul.Rubel
Rashedul.Rubel

Reputation: 3584

select round(cast(column as decimal)) from tbl will work nice But select round(column, 0) from tbl will not work that nicely because in this case 24.5000 will result 24 which should be 25 in your case.

Upvotes: 1

ANewGuyInTown
ANewGuyInTown

Reputation: 6437

You should use round(numeric_expression, 4) . Length 4 represents the length of precision for decimal values . In your case since the decimal values are of length 4, it will round according to 4 decimal values .

Upvotes: 0

Rio Ruslan
Rio Ruslan

Reputation: 51

You should use round() with 0 as [length] parameter :

round(field, 0)

SQLFiddle

Upvotes: 3

User2012384
User2012384

Reputation: 4919

Just now I took a look at this article:

https://social.technet.microsoft.com/Forums/en-us/01ca0dd6-fe5b-4ec9-9949-ce3608fcebf6/sqlround?forum=sqlservermanagementzhcht

Sorry that it isn't english, but it said:

If you need to use round, try not to use float, use decimal or numeric instead

I tried casting the field to decimal, and it works, here's the fiddle

http://sqlfiddle.com/#!9/3a01fa/4

Here's my select statement:

select round(cast(smth as decimal)) from tbl

Upvotes: 1

Related Questions