Analytics

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-08-2007
09:54 PM

07-08-2007
09:54 PM

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.^_^

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-09-2007
08:17 AM

07-09-2007
08:17 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-09-2007
07:57 PM

07-09-2007
07:57 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-12-2013
01:57 AM

12-12-2013
01:57 AM

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 .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-12-2013
04:14 AM

12-12-2013
04:14 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

12-12-2013
02:31 PM

12-12-2013
02:31 PM

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?