Round Up and Round to Nearest?

Analytics
Enthusiast

Round Up and Round to Nearest?

Dear all who know,
If I had this number, 15.4632,
If Round Up to 2 decimal, I get 15.47.
If Round to Nearest 2 decimal, I get 15.46.

How to do this 2 functions in SQL?

Many thanks in advance.^_^
5 REPLIES
Enthusiast

Re: Round Up and Round to Nearest?


There's probably a better way, but this is all I can think of on a sleepy brain ;-)

SELECT 15.4632 X, X(DECIMAL(18,2)), (X+0.005) (DECIMAL(18,2));

15.4632 15.46 15.47
Enthusiast

Re: Round Up and Round to Nearest?


From what I can tell your example shouldn't round up to 15.47.

Try playing around with the following examples.

SELECT CAST((15.4632) AS DECIMAL (6,1));
SELECT CAST((15.4632) AS DECIMAL (6,2));
SELECT CAST((15.4632) AS DECIMAL (6,3));
SELECT CAST((15.4632) AS DECIMAL (6,4));

It would be worth reading the Rounding section in Chapter 3: Decimal/Numeric Data Types of the SQL Reference:
Data Types and Literals

Joe's post is probably the best approach for manipulating the rounding results that you described.

dp
Enthusiast

Re: Round Up and Round to Nearest?

hi my datatype is decimal(8,2), iwant to insert the value from other table but i m getting numeric overflow exception, my value is like before decimal 7 digits(1111111.00)please help me how to insert.with out skip any value .

Enthusiast

Re: Round Up and Round to Nearest?

Change the data type from decimal(8,2) to decimal(9,2) and it should work. 

For details read through the following article...

http://developer.teradata.com/tools/articles/how-many-digits-in-a-decimal

Senior Apprentice

Re: Round Up and Round to Nearest?

Hi jprakash025,

did you actually read the answer to your post two days ago asking the same question?