rounding of decimals

Question

Post reply

rounding of decimals

skottikalapoodi

SSC Veteran

Points: 268
More actions
September 26, 2008 at 5:36 am

#382842

Hi,
I am trying to insert some records into another table. In this case i tried two methods
a. bulk inserting from a text file
b. inserting from a Sql Table
however when i am bulk inserting data from sql table one of decimal field value is getting rounding off
examle
source 1.57857
destination 1.5786 ....if i insert from sql table
destination 1.5785 ....if i bulk insert from text file
I want to insert data from Sql table only, but without rounding off of numbers...
can anybody help me please

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

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

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 1

If you datatype is money it will round off the 4th position after decimal.

skottikalapoodi SSC Veteran Points: 268 More actions · Answer 2

MY DATA TYPE IS DECIMAL ONLY.

ACTUALLY WHAT I WANT IS, I DON'T WANT IT TO BE ROUNDED

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

skottikalapoodi (9/26/2008)
MY DATA TYPE IS DECIMAL ONLY.
ACTUALLY WHAT I WANT IS, I DON'T WANT IT TO BE ROUNDED

Can you post the table structure?

Madhivanan

Failing to plan is Planning to fail

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Remember what the definition of DECIMAL includes:

Precision is the number of digits in a number.
Scale is the number of digits to the right of the decimal point in a number.

For example:

DECLARE @Value DECIMAL(10,6)

DECLARE @Value2 DECIMAL(10,2)

DECLARE @Value5 DECIMAL(10,5)

SET @Value = 1.125456

SET @Value2 = @Value

SET @Value5 = @Value

SELECT @Value AS 'Scale of 6', @Value2 AS 'Scale of 2', @Value5 AS 'Scale of 5'

Results in:

Scale of 6Scale of 2Scale of 5

1.125456 1.13 1.12546

Check the definition of the column in your table to insure it has the correct scale value.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

skottikalapoodi (9/26/2008)
MY DATA TYPE IS DECIMAL ONLY.
ACTUALLY WHAT I WANT IS, I DON'T WANT IT TO BE ROUNDED

DECIMAL WHAT??? What is the actual datatype of the column being imported into?;)

--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)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Assuming the data type is appropriate - you might have some luck forcing the Bulk insert or BCP to treat that column as a float through a format file (even if the actual destination column is not a float). That way - there's no attempt to round anything - it will just truncate instead.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?