September 2, 2010 at 12:11 pm
I have a table with a UnitPrice field set to the Money data type. One of my users is telling me that there are some precision issues when calculating invoices. The problem is that some products have a unit price of 0.01395.
This has 5 decimals. How can I add an extra decimal to this field? If it is not possible, would I be able to change it to say decimal(10,5)? How would this affect the rest of the data already in the table (thoudands of records).
My front end application is MS Access. Would this pose a problem because I don't think Access recognizes decimal.
September 2, 2010 at 12:36 pm
The money datatype is essentially numeric(19,4). To add a fifth decimal place, you'll have to change this to numeric(20,5).
For example:
if object_id('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
CREATE TABLE #temp (MoneyField money);
insert into #temp select 922337203685477.5807; -- max size of a money datatype
select * from #temp;
ALTER TABLE #temp ALTER COLUMN MoneyField numeric(19,4); -- 18,4 doesn't work!
select * from #temp;
ALTER TABLE #temp ALTER COLUMN MoneyField numeric(20,5);
select * from #temp;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 12:39 pm
Money is actually represented by DECIMAL(19,4). So, if you want to add another decimal digit you'd need to change the data type to DECIMAL(20,5), if you want to make sure to be able to store the equivalent of the money data type.
However, sometimes business rules might be used to limit the number of decimal places (I'd be surprised if you'd ever have a unit price of slightly less than a quadrillion $ (assuming you're not selling whole continents 😉 ).
I would add another (intermediate) column with DECIMAL(19,5) and copy the data from the money column to the new column. It doesn't matter if you go for DEC(10,5) or DEC(9,5) (edit: DEC(19,5) as the upper range, of course) in terms of storage so why not using the upper range to be as close to the money data type as possible... If everything works fine (no errors) I'd drop the money column and rename the DEC column to represent the same column name.
But you need to be aware that you can't insert a value of $100.00 anymore (which is possible with the money data type). If your application tris to insert such a value you'll get an error.
If you need to deal with such a scenario you might be better off adding a separate column holding [per piece]. So, instead of storing 0.01395 in one column you would have 1.395 in your money column and 100 in your [per piece] column. To get the price per piece you'd need to calculate it (MoneyCol/[per piece]). but this would also require a change in your app...
September 2, 2010 at 1:02 pm
Just to ensure there's no confusion, NUMERIC(19,4) and DECIMAL(19,4) are the same... or as BOL states it
numeric is functionally equivalent to decimal.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 1:10 pm
But...
DECIMAL(19,4) (or NUMERIC(19,4)) and MONEY are not, as I just googled...
Here's a sample (source: http://www.eggheadcafe.com/software/aspnet/33139628/interest-calculation.aspx):
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
It seems like SQL Server does the rounding different for money and numeric/decimal.
Good to know....
September 2, 2010 at 1:19 pm
Money and decimal(19,4) do not do exactly the same thing in calculations, as the following code demonstrates:
select
a.*,
[DecDivision]= [DecMoney]/ convert(decimal(19,4),0.9999),
[MoneyDivision] = [MoneyMoney]/ convert(money,0.9999)
from
( -- Test Data
select
[DecMoney]= convert(decimal(19,4), 19.7777 ),
[MoneyMoney]= convert(money, 19.7777 )
) a
Results:
DecMoney MoneyMoney DecDivision MoneyDivision
--------------------- --------------------- ---------------------------------------- ---------------------
19.7777 19.7777 19.7796779677967796779 19.7796
September 2, 2010 at 1:22 pm
LutzM (9/2/2010)
But...DECIMAL(19,4) (or NUMERIC(19,4)) and MONEY are not, as I just googled...
It seems like SQL Server does the rounding different for money and numeric/decimal.
Good to know....
Probably has something to do with the storage requirements... money is 8 bytes, numeric(19,x) is 9.
This relates to the max size of the money data being 922337203685477.5807, while the numeric can hold 19 9's (and you thought 5 9's was good!!!)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 2, 2010 at 1:33 pm
WayneS (9/2/2010)
LutzM (9/2/2010)
But...DECIMAL(19,4) (or NUMERIC(19,4)) and MONEY are not, as I just googled...
It seems like SQL Server does the rounding different for money and numeric/decimal.
Good to know....
Probably has something to do with the storage requirements... money is 8 bytes, numeric(19,x) is 9.
This relates to the max size of the money data being 922337203685477.5807, while the numeric can hold 19 9's (and you thought 5 9's was good!!!)
I'm slightly confused... Where did I said so? I mentioned a quadrillion before... 😉
The reason I mentioned DEC(10,5) as the "lower range" was due to the same storage size (9 byte) as for DEC(19,5). So it wouldn't make that much sense to go with a lower precision. If there was some room for interpretation I blame it to my ESEL status (or is it ESL? :-P)
September 2, 2010 at 7:41 pm
LutzM (9/2/2010)
WayneS (9/2/2010)
LutzM (9/2/2010)
But...DECIMAL(19,4) (or NUMERIC(19,4)) and MONEY are not, as I just googled...
It seems like SQL Server does the rounding different for money and numeric/decimal.
Good to know....
Probably has something to do with the storage requirements... money is 8 bytes, numeric(19,x) is 9.
This relates to the max size of the money data being 922337203685477.5807, while the numeric can hold 19 9's (and you thought 5 9's was good!!!)
I'm slightly confused... Where did I said so? I mentioned a quadrillion before... 😉
The reason I mentioned DEC(10,5) as the "lower range" was due to the same storage size (9 byte) as for DEC(19,5). So it wouldn't make that much sense to go with a lower precision. If there was some room for interpretation I blame it to my ESEL status (or is it ESL? :-P)
The 5 9's I was alluding to is the 99.999% update... Sorry for the confusion, I shouldn't have jumped thoughts like that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply