December 15, 2011 at 4:25 am
In my db I only use colums with datatype Numeric(38,8).
When I run the next select, the result is as expected (this is a strongly simplified version!)
Select convert(numeric(38,8),789012345.12345678 * 1.00000000)
result: 789012345.12345678
When I use a UDF to do this (again this is a stronly simplified version)
ALTER FUNCTION [dbo].[fntest] (@ftest as numeric(38,8))
RETURNS numeric(38,8)
AS
BEGIN
DECLARE @result as numeric(38,8)
Set @result = convert(numeric(38,8),@ftest * 1.00000000)
RETURN @Result
END
And then query the result:
Select [dbo].[fntest](789012345.12345678)
result: 789012345.12345700
I do not know why this is and how to solve it. For me it is not logical.
December 15, 2011 at 5:44 am
Wow. I have just replicated this behavior using your code.
Hmm. What version SQL Server are you using?
EDIT: In further testing, it's not the function that is the problem. Try this on for size:
DECLARE @result as numeric(38,8),@ftest as numeric(38,8)
SET @Ftest = 789012345.12345678
Set @result = convert(numeric(38,8),@ftest * 1.00000000)
SELECT @Result, convert(numeric(38,8),789012345.12345678 * 1.00000000), @ftest
Somehow the CONVERT() is causing the issue when it is set to a variable of shorter than the result of the multiplication... This bears more testing.
December 15, 2011 at 6:05 am
Thanks for your fast response.
If you leave out the convert function from the 'SET' line (Set @result = @ftest * 1.00000000), you'll get the same result too.
So, the convert doesn't create this issue.
It is the SET operation that delivers this unexpected result.
We'll that simplifies the thesis a bit
By the way we use:
- Microsoft SQL Server Standard Edition,
- version 10.50.1617.0
- Platform NT INTEL X86
December 15, 2011 at 6:10 am
I've been testing further and it turns out it's the setting of the result to the variable that's causing the issue.
Try this:
DECLARE @result as numeric(38,9),@ftest as numeric(38,8), @ResultNonConvert as numeric(38,8), @ResultBiggerNum as numeric(38,12)
SET @Ftest = 789012345.12345678
Set @result = convert(numeric(38,8),@ftest * 1.00000000)
SET @ResultNonConvert = (@ftest * 1.00000000)
SET @ResultBiggerNum = (@ftest * 1.00000000)
SELECT @Result, @ResultNonConvert, @ResultBiggerNum, @ftest
I don't know if this is a bug or not, to be honest.
December 15, 2011 at 6:19 am
Well it's the big solution I hoped for. but at least is seems to be a good workaround.
Now creating the next function with your alteration applied to it, I'm getting the right result: that is:
function:
ALTER FUNCTION [dbo].[fntest] (@ftest as numeric(38,12))
RETURNS numeric(38,8)
AS
BEGIN
DECLARE @result as numeric(38,8)
Set @result = convert(numeric(38,8),@ftest * 1.00000000)
RETURN @Result
END
Select [dbo].[fntest](789012345.12345678)
result nicely in: 789012345.12345678
December 15, 2011 at 6:21 am
I can live with this solution.
very very very very thank you very much, Brandie Tarvin
greetings Marc
December 15, 2011 at 6:30 am
I think this thread from StackOverflow may explain the behavior you're seeing:
http://stackoverflow.com/questions/126401/sql-server-2005-numeric-precision-loss
From that thread:
Since you multiplied NUMERIC(24,8) and NUMERIC(24,8), and SQL Server will only check the type not the content, it probably will try to save the potential 16 non-decimal digits (24 - 8) when it can't save all 48 digits of precision (max is 38). Combine two of them, you get 32 non-decimal digits, which leaves you with only 6 decimal digits (38 - 32).
December 15, 2011 at 6:40 am
that explaines it indeed and it also shows why this workaround works. Or rather: It shows why this solution is even necessary to solve this issue.
thx!
December 15, 2011 at 7:29 am
That is good information, Jon. Thanks for the link!
EDIT: Although, this issue probably is something that needs reported to Microsoft to see if they'll fix it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply