Convert number without rounding

Question

Post reply

Convert number without rounding

vmon

SSChasing Mays

Points: 624
More actions
October 25, 2006 at 12:33 pm

#116161

I have a decimal column and it contains 1.7325000000 that I want to change that number to be 1.45600CONVERT(NUMERIC(18,3),UnitPriceSell) AS NewUnit
I am trying to use the following convert statement.
CONVERT(NUMERIC(18,3),UnitPriceSell) AS NewUnit

But I am getting the number 1.733 I don't want it to round the numbers. What I actuall want is this 1.7320000000

What am I doing wrong?

vmon

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

declare @decimal decimal(18,10)

set @decimal = 1.7325000000

SELECT SUBSTRING(CAST(@decimal as varchar),1,(CHARINDEX('.',@decimal) + 3)) AS NewUnit

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Actually rather simple, try this:

round(@value, 3, 1)

happy hunting!

Prema sambandam SSCommitted Points: 1825 More actions · Answer 3

Try this:

Select convert (numeric(18,7),unitsellprice) as newunit.

Since you specify the precision value to 3 digits, it rounded the value upto 3 digits.If you want to display the precision value more than 3 , you have to increase the precision value.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Prema,

That would work if he wanted 1.7325 to display as 1.7325 as 1.7325. He wanted 1.7325 to display as 1.732 not as 1.733. Using the round function - round(unitsellprice, 3, 1) will return the value 1.732 when unitsellprice is 1.7325.

happy hunting

Jeff Moden SSC Guru Points: 1003946 More actions · Answer 5

Lynn is absolutely correct... for those that can't find the ROUND function in Books Online here's what the 3rd operand of "1" means...

function

Is the type of operation to perform. function must be tinyint, smallint, or int. 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.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)