CODEWITHSUNDEEP
sql-serverrounding
Santosh
Santosh

Reputation: 2515

How to use ROUNDDOWN in sqlserver

I want to use ROUNDDOWN function.

When i tried using the following query,it gives me an error saying "'rounddown' is not a recognized built-in function name."

select rounddown(25.227,2)

My requirement is to rounddown the value to two decimals

for ex: for value 25.22789 result should be 25.22

and round up also

for ex: for value 25.22789 result should be 25.23

Any help?

Thanks in advance

Upvotes: 11

Views: 72649

Answers (6)

Colin Lusk
Colin Lusk

Reputation: 1

Greetings from the future.

Multiply it up, divide it back down?

declare @myroundednumber numeric(9,4)=25.227
select 'Original' version,@myroundednumber figure 
select 'Rounded Up',cast(ceiling(25.227 *100)/100 as decimal(9,2)) 
select 'Rounded Down',cast(floor(25.227 *100)/100 as decimal(9,2))

Upvotes: 0

Kaf
Kaf

Reputation: 33839

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

Fiddle demo

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 |

Upvotes: 23

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9063

ROUND FUNCTION

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

As per ROUND description:

ROUND ( numeric_expression , length [ ,function ] )

When 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 following if you want to do "ROUND DOWN" and use 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

As per comments about ROUNDUP you can use in following:

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

RESULT

25.23

DEMO

You can test it at SQL FIDDLE

Upvotes: 3

M_Idrees
M_Idrees

Reputation: 2182

Also you can use CAST:

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

Upvotes: 0

Wonko the Sane
Wonko the Sane

Reputation: 10823

For rounding down, just use some simple math (one decimal place farther than you want to round to):

SELECT ROUND(25.22789 - 0.005, 2)

OUTPUT 25.22

For rounding up, simply use ROUND:

SELECT ROUND(22.22789, 2)

OUTPUT 25.23

Upvotes: 2

Rahul
Rahul

Reputation: 77936

I think you are looking for either CEILING() or floor() function like

select CEILING(25.227) //results in 26

(OR)

select FLOOR(25.227) //Results in 25

EDIT:

for ex: for value 25.22789 result should be 25.22

You can try like below

select round(25.22789, 2, 2)

Which will result in 25.22000

Upvotes: 12

Related Questions