Rounding Error Converting Char data to Money data

Question

Post reply

Rounding Error Converting Char data to Money data

Ken Powers

SSCommitted

Points: 1561
More actions
April 18, 2005 at 7:36 am

#65643

I have an a database which uses the money datatype. The database receives text files which are loaded into the table. In the process of loading, some of the data is converted from character data to money data. I was recently informed that some of the data was improperly rounding the data. For example, "349" was converted to $3.00 instead of $3.49. Is there any way to convert this data without losing the cents?

“If you're not outraged at the media, you haven't been paying attention.”

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

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Can you post a bit of your code? You seem to inserting the decimal and that may involve a ROUNDing function.

Thanks

I wasn't born stupid - I had to study.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Depending on what kind of calculations you perform on your data, MONEY might *not* be the best choice around. Addition and Subtraction is quite okay, however, it gets tricky when multiplication and division are involved. Consider this:

declare @m1 money, @m2 money, @m3 money

declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)

set @m1 = 1.00

set @m2 = 345.00

set @m3 = @m1/@m2

set @d1 = 1.00

set @d2 = 345.00

set @d3 = @d1/@d2

select @m3, @d3

--------------------- ---------------------

.0028 .0029

(1 row(s) affected)

Both calculation are correct according to the data type specifications. However, MONEY is off by more than 3% from the "correct" answer. Might be a thing to consider.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ken Powers SSCommitted Points: 1561 More actions · Answer 3

The code is part of a DTS package. As you can see, I'm taking the value from the text file and dividing it by 100:

'**********************************************************************

' Visual Basic Transformation Script

'************************************************************************

' Copy each source column to the destination column

Function Main()

DTSDestination("Zone3Price") = (DTSSource("Col011")\100)

Main = DTSTransformStat_OK

End Function

This is the DDL of the table:

CREATE TABLE [dbo].[RetailInformationBuffer] (

[Division] [char] (3) NOT NULL ,

[UPC] [numeric](14, 0) NOT NULL ,

[WarehouseNumber] [char] (3) NOT NULL ,

[CommCode] [char] (5) NOT NULL ,

[WEDateTS] [datetime] NOT NULL ,

[Zone1Quan] [int] NULL ,

[Zone1Price] [money] NULL ,

[Zone2Quan] [int] NULL ,

[Zone2Price] [money] NULL ,

[Zone3Quan] [int] NULL ,

[Zone3Price] [money] NULL ,

[Zone4Quan] [int] NULL ,

[Zone4Price] [money] NULL ,

[Zone5Quan] [int] NULL ,

[Zone5Price] [money] NULL ,

[Zone6Quan] [int] NULL ,

[Zone6Price] [money] NULL ,

[Zone7Quan] [int] NULL ,

[Zone7Price] [money] NULL ,

[Zone8Quan] [int] NULL ,

[Zone8Price] [money] NULL ,

[Zone9Quan] [int] NULL ,

[Zone9Price] [money] NULL ,

[Zone10Quan] [int] NULL ,

[Zone10Price] [money] NULL ,

[Zone11Quan] [int] NULL ,

[Zone11Price] [money] NULL ,

[Zone12Quan] [int] NULL ,

[Zone12Price] [money] NULL ,

[Zone13Quan] [int] NULL ,

[Zone13Price] [money] NULL ,

[Zone14Quan] [int] NULL ,

[Zone14Price] [money] NULL ,

[Zone15Quan] [int] NULL ,

[Zone15Price] [money] NULL ,

[Zone16Quan] [int] NULL ,

[Zone16Price] [money] NULL

)

“If you're not outraged at the media, you haven't been paying attention.”

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Try this in query analyzer and I think you will see your difficulty:

SELECT CONVERT( money, '349') /100

SELECT '349' / 100

You probably want to convert your Col011 before dividing by 100.

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 5

Look at your code:

DTSSource("Col011")\100

Is using integer division you should change it to Real Division

DTSSource("Col011")/100

HTH

* Noel

Ken Powers SSCommitted Points: 1561 More actions · Answer 6

It all makes sense now. Thanks noeld.

The developer reported that only one column, Zone3Price, was converting incorrectly. This didn't make sense to me, because the conversion was supposed to be the same for each money column. After reading your reply, I checked the code for each transformation task, and that's when I noticed that I had reversed the division sign on the transformation for Zone3Price.

Good to have a second set of eyes look at my code.

“If you're not outraged at the media, you haven't been paying attention.”

noeld SSC Guru Points: 96590 More actions · Answer 7

When these things happen to me is usually an indication of lack of Caffeine in my veins

I get a cup or two and it normally helps finding those

Cheers!

* Noel