# Change Data Type of data retrieved by SELECT query • ### Question

• There is a SQL money column in one of my tables (Profit) which contains some large values(100,000,000,000,000.00 , 123,456,000,789.00 etc.)

In one of the stored procedures I need to take few columns of above table(Profit) and return the sum of them. At that step, it gives an error "Arithmetic overflow error converting expression to data type money.".

Can any one please tell me how can I avoiding this error without changing the data type of that particular column.

Monday, May 9, 2011 1:07 PM

• The maximum value of MONEY data type is

922,337,203,685,477.5807

If your sum is more than this value, you get this error.

You may convert the values a numeric format before SUMming like:

SUM(convert(decimal(32, 4), MoneyValue)) or

convert(decimal(32, 4), MoneyValue1) + convert(decimal(32, 4), MoneyValue2) + ...

Use the precision and scale as per requirement..

The simpler the solution the stronger it is
If this post is useful, please vote it as useful..
• Proposed as answer by Monday, May 9, 2011 1:34 PM
• Marked as answer by Tuesday, May 10, 2011 5:18 AM
Monday, May 9, 2011 1:22 PM

### All replies

• The maximum value of MONEY data type is

922,337,203,685,477.5807

If your sum is more than this value, you get this error.

You may convert the values a numeric format before SUMming like:

SUM(convert(decimal(32, 4), MoneyValue)) or

convert(decimal(32, 4), MoneyValue1) + convert(decimal(32, 4), MoneyValue2) + ...

Use the precision and scale as per requirement..

The simpler the solution the stronger it is
If this post is useful, please vote it as useful..
• Proposed as answer by Monday, May 9, 2011 1:34 PM
• Marked as answer by Tuesday, May 10, 2011 5:18 AM
Monday, May 9, 2011 1:22 PM
• Cast the column value to a data type that can accomodate the SUM, like numeric.

```SELECT
SUM(CAST(c1 AS numeric(25, 4))) AS sum_c1
FROM
(
SELECT CAST(100000000000000.00 AS money) AS c1 UNION ALL
SELECT CAST(900023456000789.00 AS money) AS c1 UNION ALL
SELECT CAST(99999999999999.00 AS money) AS c1
) AS T;
GO
```

You can also store the value in thousands or millions, if those numbers will be always that big.

AMB

Some guidelines for posting questions...

Monday, May 9, 2011 1:28 PM
• Other posted good answers. I just wanted to let you know that you should be sceptical in cases like this. I would not quickly use it (because I prefer Decimal), but if I used it, it would only be for real money amounts.

Just to give you an idea of reality. The current national debt of the US is \$14,337,212,979,329.65  So in the money data type you can fit that debt 65 times. It is unlikely that you really have money amounts of 100 trillion, or a total that exceeds 922 trillion. If you actually have a "Profit" of 100 trillion, then you don't have to worry about such an overflow situation.

Gert-Jan
• Proposed as answer by Monday, May 9, 2011 6:33 PM
Monday, May 9, 2011 6:25 PM
• Thank you very much for your help

:)

Tuesday, May 10, 2011 5:18 AM