October 12, 2009 at 5:41 am
Hi,
I have a scenario where a division is giving me a value greater than I provision for.
Is it possible to CAST or Convert to my desired result?
In the code below I need the "Weight" value to be of type Decimal (18,17)
When I try cast (see commented code) I get error
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Here is a sample of my data:
CREATE TABLE #tempValues
(
Value1 DECIMAL(18,2),
Value2 DECIMAL(18,2)
)
INSERT INTO #tempValues
SELECT 592.40,658.17
UNION ALL
SELECT 9776628.00,75.67
SELECT
Value1,
Value2,
Value1/Value2 AS [Weight],
LEN(Value1/Value2) AS Length
--CAST(Value1/Value2 AS DECIMAL(18,17))
FROM #tempValues
Thank you for taking time to view my problem!
October 12, 2009 at 5:46 am
Are you sure you want / need a decimal(18,17).
Thats an 18 digit number with a precision of 17 decimal places.
ie
1.12345678901234567
October 12, 2009 at 5:59 am
I'm working on an old stored proc; the author of which I will not be able to get hold of.
I suppose I could reduce the precision but that could potentially open up another can of worms.
October 12, 2009 at 6:12 am
So .... what do you expect and want to happen if the resulting number cant fit into the storage space ?
October 12, 2009 at 6:50 am
Bear with me for a sec.
The result of Value1/Value2 is 129200.84577771904321395533
What I would like is to fit this into a Decimal(18,17)
The following works:
CAST(Value1/Value2 AS DECIMAL(18,12))
But the following gives the overflow error:
CAST(Value1/Value2 AS DECIMAL(18,17))
I may be lacking the basics of how CAST is supposed to operate.
If the result can be cast to a DECIMAL (18,12) why not a DECIMAL(18,17) ?
October 12, 2009 at 6:59 am
Grinja (10/12/2009)
Bear with me for a sec.The result of Value1/Value2 is 129200.84577771904321395533
What I would like is to fit this into a Decimal(18,17)
The following works:
CAST(Value1/Value2 AS DECIMAL(18,12))But the following gives the overflow error:
CAST(Value1/Value2 AS DECIMAL(18,17))I may be lacking the basics of how CAST is supposed to operate.
If the result can be cast to a DECIMAL (18,12) why not a DECIMAL(18,17) ?
Because DECIMAL(18,17) says that the numeric value is 18 digits long with 17 of them to the right of the decimal point. DECIMAL (18,12) says that the numeric value is 18 digits long with 12 of them to the right of the decimal point and 6 of them to the left.
DECIMAL(18,17) is reserving 1 digit for the integer portion of the data. You need 6.
October 12, 2009 at 7:03 am
Great thanks for clearing that one up for me!
Sorry Dave, I see you had already indicated that in your first post. I need more coffee...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply