none
Arithmetic overflow error converting numeric to data type numeric. RRS feed

  • Question

  • Hi All,

        I have a column of Datatype Decimal(19,2), not null. I have the following query with me

    SELECT

    Cast(58946359337193.36 As Decimal(12

    ))

     Arithmetic overflow error converting numeric to data type numeric.

     

    And because of this, the data in my cube and table doesn't match as there is differnce in the values after decimal. So, how to get rid of this?

     

    Thanks
    Bangaaram


    Known is a DROP, Unknown is an OCEAN.
    Friday, January 27, 2012 3:42 PM

Answers

  • When you tell it to cast to Decimal(14), you are telling SQL Server you want 14 digits to the left of the decimal point and 0 to the right.

    SELECT Cast (58946359337193.36 As Decimal( 14))

    You want something like:

     

    SELECT Cast (58946359337193.36 As Decimal( 16,2))

    16 total digits, 14 to the left, and 2 to the right of the decimal point


    Dave Frommer - BI Architect - Independent
    • Marked as answer by Bangaaram Wednesday, February 1, 2012 4:19 PM
    Wednesday, February 1, 2012 4:11 PM

All replies

  • I am not following your problem / question.

    > So, how to get rid of this?

    SELECT CAST(58946359337193.36 AS decimal (19)) AS c1;
    GO

    > And because of this, the data in my cube and table doesn't match as there is differnce in the values after decimal

    - What cube?

     


    AMB

    Some guidelines for posting questions...

    Friday, January 27, 2012 3:46 PM
    Moderator
  • I am not following your problem / question.

    > So, how to get rid of this?

    SELECT CAST(58946359337193.36 AS decimal (19)) AS c1;
    GO

    > And because of this, the data in my cube and table doesn't match as there is differnce in the values after decimal

    - What cube?

     


    AMB

    Some guidelines for posting questions...


    When I run this, it gave me values only before the decimal.

    We have the same kind of data in Analysis Service Cube and will cross check the data with that in cube. So, the values after the decimal in cube are being rounded off and it doesn't match with the value of table.


    Known is a DROP, Unknown is an OCEAN.
    Friday, January 27, 2012 4:13 PM
  • > We have the same kind of data in Analysis Service Cube

    Are you setting the proper type in SSAS?

     


    AMB

    Some guidelines for posting questions...

    Friday, January 27, 2012 4:26 PM
    Moderator
  • > We have the same kind of data in Analysis Service Cube

    Are you setting the proper type in SSAS?

     


    AMB

    Some guidelines for posting questions...


    In Analysis Services, it is having a datatype of Currency. I am sorry that I forgot to mention I am using SQL Server 2000.
    Known is a DROP, Unknown is an OCEAN.
    Friday, January 27, 2012 4:34 PM
  • try this

    SELECT

    Cast

     

    (58946359337193.36 As Decimal(

    14

    ))

     

     

    Wednesday, February 1, 2012 11:40 AM
  • try this

    Cast

     

    (58946359337193.36 As Decimal(

    14

    ))

     

     

     

     

    SELECT


    No use of it. I wan the values after decimal. It gave me the following output.


    Known is a DROP, Unknown is an OCEAN.
    Wednesday, February 1, 2012 2:26 PM
  • Any progress?
    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    No. Do you have any idea?
    Known is a DROP, Unknown is an OCEAN.
    Wednesday, February 1, 2012 2:27 PM
  • When you tell it to cast to Decimal(14), you are telling SQL Server you want 14 digits to the left of the decimal point and 0 to the right.

    SELECT Cast (58946359337193.36 As Decimal( 14))

    You want something like:

     

    SELECT Cast (58946359337193.36 As Decimal( 16,2))

    16 total digits, 14 to the left, and 2 to the right of the decimal point


    Dave Frommer - BI Architect - Independent
    • Marked as answer by Bangaaram Wednesday, February 1, 2012 4:19 PM
    Wednesday, February 1, 2012 4:11 PM
  • When you tell it to cast to Decimal(14), you are telling SQL Server you want 14 digits to the left of the decimal point and 0 to the right.

    SELECT Cast (58946359337193.36 As Decimal( 14))

    You want something like:

     

     

    SELECT Cast (58946359337193.36 As Decimal( 16,2))

    16 total digits, 14 to the left, and 2 to the right of the decimal point


    Dave Frommer - BI Architect - Independent


    Thanks. But, I have the Data Type Currency for the same data in Cube and Data Type Decimal(19,2) for the data in Table. So, when the results of both the Cube & Table were compared there was some precision mismatch after decimal and because of that it always throw me error if the figures doesn't match exactly. So, is there a way you can overcome that situation?

     

    Thanks
    Bangaaram


    Known is a DROP, Unknown is an OCEAN.
    Wednesday, February 1, 2012 4:21 PM