Arithmetic overflow error converting numeric to data type numeric for Decimal(25,12)

Question

I know the question is a duplicate, but I am not able to find a solution to my problem below:

                SELECT
                CAST(747448809352908.49434500000 AS  Decimal(25,12)) AS [Value]

This is the value I am getting from one of my calculations. I am trying to insert into a Table with Value column designed as Decimal(25,12). With a value as above I am getting an overflow error.

Please assist. The problem being I cannot update the settings of the column. Therefore I need to fit into the the column.

I cannot can I??

possible duplicate of Decimal (10,9) variable can't hold the number 50 (SQL Server 2008) — A Hocevar
– A Hocevar, Commented Aug 18, 2015 at 8:47

wiretext · Accepted Answer · 2015-08-18 08:51:27Z

2

your total length of value is not matched with your data type. you are assigning Decimal(25,12) but length more than 25, actually 27

SELECT CAST('747448809352908.49434500000' AS  Decimal(27,12)) AS [Value]

In SQL Server, decimal and numeric are numeric data types that have fixed precision and scale.

For a decimal(5,2) variable, it can only hold three (5-2) digits before the decimal point and two digits after the decimal point. However, “1234” has four digits before the decimal point so it exceeds the precision and the error occurs.

The same issue happens if you assign an integer value 1234 to the decimal(5,2). decimal and numeric

edited Aug 18, 2015 at 8:51

answered Aug 18, 2015 at 8:43

wiretext

3,35216 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

d_luffy_de Over a year ago

Looks like I have no other option that to increase the column size. Thanks

wiretext Over a year ago

@DarshanBI-DW if last zeros(00000) are not useful then you can remove it so your objective will satisfy but if needed

Luaan · Accepted Answer · 2015-08-18 08:45:32Z

1

Your number is too big for decimal(25, 12). Count with me - there's 15 digits left of the decimal point, and your precision is 25. 12 of those 25 is for decimals, so you're left with 13. 13 is less than 15 -> arithmetic overflow.

decimal is not a floating point number. Its precision and scale is fixed - you defined that your number has 13 digits left of the decimal point, and twelve right. That's all you have to work with. The fact that some of the decimals are zero is irrelevant - decimal can't "shift" the decimal point at will. It's fixed.

answered Aug 18, 2015 at 8:45

Luaan

64.1k7 gold badges108 silver badges126 bronze badges

Comments

Moudiz · Accepted Answer · 2015-08-18 08:51:09Z

1

tinka answer is correct but here is explanation

the length of this 747448809352908.49434500000 is 27 so in DECIMAL should contain the maximum length of the number , so the error because you specify 25 , the number 12 is the number after the comma 49434500000 whic is 11.

answered Aug 18, 2015 at 8:51

Moudiz

7,42723 gold badges87 silver badges166 bronze badges

Comments

Rush.2707 · Accepted Answer · 2015-08-18 08:53:28Z

0

The number you entering is too big. You might reffer this [link]: https://msdn.microsoft.com/en-us/library/ms187928.aspx if you change the number below 11 in place of 12 then you will get some answer. so try to decrease number.

answered Aug 18, 2015 at 8:53

Rush.2707

68310 silver badges30 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric to data type numeric for Decimal(25,12)

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related