Arithmetic Overflow error converting varchar to numeric when using IsNumeric in SQL

Question

When I try to insert into a temporary table a decimal, I get an arithmetic overflow error:

Arithmetic overflow error converting varchar to data type numeric.

This is the SQL:

CREATE TABLE #ObDiabetesEncounterIDs
(
     PatientEncounterID int, 
     Value decimal(5,2)
)

INSERT INTO #ObDiabetesEncounterIDs(PatientEncounterID, Value)
    SELECT PatientEncounterID, Value 
    FROM Observation 
    WHERE Term = 'HGBA1C' 
      AND ISNUMERIC(Value) = 1 
      AND Value IS NOT NULL 
      AND PatientEncounterID IS NOT NULL;

Most of the decimal values are between 5.1 to 15.1. There are a few values that are whole numbers like 15 and 10. There are 2 that are out of that range such as 1405 and 151. I thought I accommodated these values by using the data type decimal(5,2).

Where am I going wrong?

UPDATE Updating the precision of the decimal helped. This accounted for the 1 record that was 1403. But there is also 1 record that has a comma instead of a period. So, the value is 9,7. So, I believe I will need to check for ISNUMERIC then change any commas to periods, then cast as a decimal.

DECIMAL(5,2) has 5 digits overall, of which 2 are after the decimal point - so you can store values from 0.00 to 999.99 in that type - a value like 1405 CANNOT be stored in such a decimal column! — marc_s
– marc_s, Commented Jan 27, 2017 at 6:04

STLDev · Accepted Answer · 2017-01-27 19:44:21Z

1

Since Value is character data, cast it to DECIMAL in your SELECT statement:

CREATE TABLE #ObDiabetesEncounterIDs(PatientEncounterID int, Value decimal(7,2));

INSERT INTO #ObDiabetesEncounterIDs(PatientEncounterID, Value)
SELECT PatientEncounterID, CAST(Value AS DECIMAL(7,2))
  FROM Observation 
  WHERE Term = 'HGBA1C' 
    AND Value IS NOT NULL 
    AND PatientEncounterID IS NOT NULL;

To test this, I did the following (Note that in the following example I named the source table MyObservation instead of Observation so as not to interfere with the original table):

CREATE TABLE [dbo].[MyObservation](
    [PatientEncounterID] [int] IDENTITY(1,1) NOT NULL,
    [Value] [varchar](50) NULL,
    [Term] [varchar](50) NULL,
    CONSTRAINT [PK_MyObservation] PRIMARY KEY CLUSTERED (
        [PatientEncounterID] ASC
    )WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO [dbo].[MyObservation]
    (Value, Term)
    Values ('.01', 'HGBA1C')
        ,('.12', 'HGBA1C')
        ,('1.23', 'HGBA1C')
        ,('12.34', 'HGBA1C')
        ,('123.45', 'HGBA1C')
        ,('1234.56', NULL)
        ,('12345.67', 'HGBA1C')
        ,('non-numeric', 'HGBA1C')
        ,('9.98', 'Not HGBA1C')
        ,('123.4958', 'HGBA1C')

CREATE TABLE #ObDiabetesEncounterIDs(
    PatientEncounterID int, Value decimal(7,2)
);

INSERT INTO #ObDiabetesEncounterIDs(PatientEncounterID, Value)
SELECT PatientEncounterID, CAST(Value AS DECIMAL(7,2))
    FROM [dbo].[MyObservation]
    WHERE Term = 'HGBA1C' 
    AND ISNUMERIC(Value) = 1
    AND Value IS NOT NULL 
    AND PatientEncounterID IS NOT NULL;

SELECT * FROM #ObDiabetesEncounterIDs

The above worked flawlessly on my SQL Server 2014 system.

edited Jan 27, 2017 at 19:44

answered Jan 27, 2017 at 4:17

STLDev

6,17829 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gloria Santin Over a year ago

Thanks. Let me try that.

Gloria Santin Over a year ago

It is still returning an overflow error converting varchar to data type numeric. I even added ISNUMERIC(Value) = 1 to the query and the error is still happens. Shouldn't IsNumeric() suppose to return only values in the column that are numeric and can be CAST as a decimal?

STLDev Over a year ago

Would you please post at least a partial schema of your Observation table showing the columns PatientEncounterID and Value? It also would be worthwhile if you could post a few sample values.

Gloria Santin Over a year ago

This answer will work. The problem with my data is that there was a comma in one of the rows (9,7) and data with a comma and a period (6.,7). Once I replaced those, it worked. Thanks.

STLDev Over a year ago

I've worked up a full example, above. There must be an issue with your data. Find offending records by this query:

SELECT PatientEncounterId, Value, Term FROM [Observation] WHERE NOT(Term='HGBA1C' AND ISNUMERIC(Value)=1 AND Value IS NOT NULL AND PatientEncounterID IS NOT NULL);

STLDev · Accepted Answer · 2017-01-27 03:35:10Z

0

Your type is wrong. decimal(5,2) is for 3 digits to the right of the decimal and 2 digits to the left. Try changing that to decimal(7,2).

answered Jan 27, 2017 at 3:35

STLDev

6,17829 silver badges37 bronze badges

4 Comments

Gloria Santin Over a year ago

I get the error, Error converting data type varchar to numeric. However, the documentation states that the first number is precision and the second is scale. decimal[ (p[ ,s] )] p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point.

STLDev Over a year ago

You may want to modify your strategy such that you could determine what row of input data is offensive to the temp table. Once you have that information it may help you determine the exact issue. Also, what are the data types of the source table?

Gloria Santin Over a year ago

The data type of the source table is a varchar. The column can hold all types of data. I thought using ISNUMERIC() = 1 will only return a number.

STLDev Over a year ago

Sorry - didn't realize the input data type. I'll modify my answer.

Collectives™ on Stack Overflow

Arithmetic Overflow error converting varchar to numeric when using IsNumeric in SQL

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related