# Arithmetic overflow error converting numeric to data type numeric.

• ### 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

• 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 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
• 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
• > 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 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