locked
Change Data Type of data retrieved by SELECT query RRS feed

  • 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

Answers

  • The maximum value of MONEY data type is

    922,337,203,685,477.5807

    See doc: http://msdn.microsoft.com/en-us/library/ms179882.aspx

     

    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 answers you, please mark it as answer..
    If this post is useful, please vote it as useful..
    • Proposed as answer by Naomi N Monday, May 9, 2011 1:34 PM
    • Marked as answer by Sachitra 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

    See doc: http://msdn.microsoft.com/en-us/library/ms179882.aspx

     

    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 answers you, please mark it as answer..
    If this post is useful, please vote it as useful..
    • Proposed as answer by Naomi N Monday, May 9, 2011 1:34 PM
    • Marked as answer by Sachitra 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 Naomi N 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