算术溢出错误将数字转换为数据类型数字。 [英] Arithmetic overflow error converting numeric to data type numeric.

问题描述

嗨专家，



根据我的理解 NUMERIC（18,10）列需要18位小数小数点左边的数字和右边的10位数。



我们的一个程序开始抛出算术溢出错误，将数字转换为数据类型数字错误较小的价值观。我查看了msdn http://msdn.microsoft.com/en-us/library/ms187746中的文档。 aspx [ ^ ]哪个似乎也说了同样的话。我在这里想念一下吗？



示例：

Hi experts,

As per my understanding NUMERIC(18, 10) column would take 18 decimal digits to the left of the decimal point and 10 to the right.

One of our procedure started throwing 'Arithmetic overflow error converting numeric to data type numeric' error for much lesser values. I checked the documentation in msdn http://msdn.microsoft.com/en-us/library/ms187746.aspx[^] which also seems to say the same thing. Is there something I miss here?

Example:

SELECT CAST(1234567891234567.34 AS NUMERIC(18,2))  --Works
SELECT CAST(1234567891234567.34 AS NUMERIC(18,10)) --fails with Arithmetic overflow error

我们通过将转换更改为 NUMERIC（18,2）来解决问题，但我想了解问题更深入。

We fixed the problem by changing the casting to NUMERIC(18, 2) but I wanted to understand the problem in a little more depth.

推荐答案

阅读本文：

http://msdn.microsoft.com/en-us/library/ms187746.aspx [ ^ ]



NUMERIC（p，s）： p表示小数点左边和右边



一般来说，在同一个上下文中不能有大数字和小数字（处理数百万

Read this :
http://msdn.microsoft.com/en-us/library/ms187746.aspx[^]

NUMERIC (p,s) : p means both left and right of the decimal

Generally you can't have large numbers and small numbers with in the same context (handling millions of

并跟踪1/10000美分等因为它们在这些尺度上没有任何差别。）

's and keeping track of 1/10000 of cents etc. as it would not make any difference at those scales).

给出的解决方案1 Mehdi Gholam 非常好。



我想添加以下内容，因为OP想要明确知道为什么在第一种情况下没有抛出错误以及为什么在第二种情况下抛出错误。 />


从陈述中可以看出

The Solution 1 given by Mehdi Gholam is very good.

I want to add the following since the OP wants to know specifically why error was not thrown in the first case and why error is thrown in the second case.

As seen from the statements

SELECT CAST(1234567891234567.34 AS NUMERIC(18,2))  --Works
SELECT CAST(1234567891234567.34 AS NUMERIC(18,10)) --fails with Arithmetic overflow error

很明显，在第一种情况下，语句 NUMERIC（18,2）小数点左边有18位和16位数字，而 1234567891234567 是16位数。因此，没有错误。

在第二种情况下，从语句 NUMERIC（18,10）开始，左边有8位数字十进制，但 1234567891234567 是16位数，超过8位数。因此，发生算术溢出错误。

it is clear that, in the first case from the statement NUMERIC(18,2) the total digits are 18 and 16 digits are available to the left of decimal, whereas 1234567891234567 are 16 digits. Hence, there is no error.
In the second case, from the statement NUMERIC(18,10), 8 digits are available to the left of decimal, but 1234567891234567 are 16 digits which is more than 8 digits. Hence, Arithmetic overflow error occurs.

这篇关于算术溢出错误将数字转换为数据类型数字。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！