April 18, 2005 at 7:36 am
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.”
April 18, 2005 at 7:39 am
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.
April 18, 2005 at 7:45 am
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]
April 18, 2005 at 9:22 am
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.”
April 18, 2005 at 10:17 am
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.
April 18, 2005 at 11:10 am
Look at your code:
DTSSource("Col011")\100
Is using integer division you should change it to Real Division
DTSSource("Col011")/100
HTH
* Noel
April 18, 2005 at 11:24 am
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.”
April 18, 2005 at 11:31 am
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