3 comments
Various 
This might be something most developers are already aware of, but I have been familiar with SQL Server mostly as a DBA, not actively busy with the meaning of data and it therefore came as a complete surprise to me.
Question: How much is $1/$345, rounded at 4 decimals?
Take a calculator and it will tell you: 0.0029.
Take Excel and it will tell you: 0.0029.
Take SQL Server and it will tell you: 0.0028.
Euh… wait a minute… did you say 0.0028? Isn’t that 3-4% off?
Seems SQL Server, when using the MONEY datatype, doesn’t round values, but truncates them instead. See the example below. I think it’s pretty shocking.
declare @m1 money, @m2 money, @r1 decimal(19,4)
declare @d1 decimal(19,4), @d2 decimal(19,4), @r2 decimal(19,4)
declare @f1 float, @f2 float, @r3 float;
set @m1 = 1.00;
set @m2 = 345.00;
set @r1 = @m1/@m2;
set @d1 = 1.00;
set @d2 = 345.00;
set @r2 = @d1/@d2;
set @f1 = 1.00;
set @f2 = 345.00;
set @r3 = @f1/@f2;
select @r1, @r2, @r3;
3 comments
What about this:
SELECT CAST(1.00/345.00 AS MONEY)
SELECT CAST(1.00 AS MONEY)/CAST(345.00 AS MONEY)
DataDude
What about:
SELECT CAST(1.00/345.00 AS MONEY)
SELECT CAST(1.00 AS MONEY)/CAST(345.00 AS MONEY)
DataDude
My example was to clarify a problem with working with values stored as money in your database.
The first example is therefor not relevant, as the operation works with decimals before it’s converted to money.
The second example still truncates.
A solution would be:
declare @m1 money, @m2 money, @r1 decimal(19,4)
set @m1 = 1.00;
set @m2 = 345.00;
set @r1 = CAST(@m1 AS DECIMAL(19,4)) / CAST(@m2 AS DECIMAL(19,4));
SELECT @R1
But then why not store monetary values as DECIMAL in the first place?
robp