Geek Answers Handbook

How to use ROUNDDOWN in sqlserver

I want to use the ROUNDDOWN function.

When I tried to use the following query, it gives me the error message "rounddown" is not a recognized built-in function name.

select rounddown(25.227,2)

My requirement is to round the value to two decimal places

for ex: for a value of 25.22789, the result should be 25.22

and also round

for ex: for a value of 25.22789, the result should be 25.23

Any help?

Thank you in advance

+6
source share
5 answers

Use the third parameter ROUND() to truncate and then CONVERT() to DECIMAL(x, 2) to get rid of unwanted trailing zeros.

Demo screenshot

 SELECT CONVERT(DECIMAL(10,2), ROUND(25.227, 2, 1)) RoundDown, CONVERT(DECIMAL(10,2), ROUND(25.227, 2, 0)) RoundUp

results

 | RoundDown | RoundUp | |-----------|---------| | 25.22 | 25.23 |
+5
source

I think you are looking for the function CEILING() or floor() , for example

 select CEILING(25.227) //results in 26

(OR)

 select FLOOR(25.227) //Results in 25

EDIT:

for ex: for a value of 25.22789, the result should be 25.22

You can try as below

 select round(25.22789, 2, 2)

This will result in 25.22000

+3
source

To round, use a simple math (one decimal place further than you want to round):

SELECT ROUND(25.22789 - 0.005, 2)

EXIT 25.22

For rounding just use ROUND:

SELECT ROUND(22.22789, 2)

EXIT 25.23

+1
source

ROUND FUNCTION

You can use SELECT ROUND(@num, 2, 1)

According to ROUND description:

 ROUND ( numeric_expression , length [ ,function ] )

If the function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

RESULT

 25.22

CHARINDEX and LEFT

Alternatively, you can use CHARINDEX and LEFT in the following if you want to do "ROUND DOWN" and use a simple ROUND to do "ROUND UP"

 DECLARE @num DECIMAL(16,3) = 25.227 SELECT LEFT(@num,CHARINDEX('.',@num)+2) as [RoundDown], ROUND(@num,2) as [RoundUp]

RESULT

 RoundDown RoundUp 25.22 25.23

UPDATE

According to the comments on ROUNDUP you can use the following:

 SELECT ROUND(22.22289 + 0.005, 2) as [RoundUp1]

RESULT

 25.23

Demo

You can check it on SQL FIDDLE

0
source

You can also use CAST:

  declare @num decimal(18,5) = 22.22719 select @num, CAST(@num as decimal(18,3))
0
source

More articles:


All Articles