Rounding Error Converting Char data to Money data

  • 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.”

  • 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.

  • 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]

  • 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.”

  • 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.

  • Look at your code:

    DTSSource("Col011")\100

    Is using integer division you should change it to Real Division  

    DTSSource("Col011")/100

    HTH

     


    * Noel

  • 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.”

  • 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

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

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