Ошибка арифметического переполнения при преобразовании nvarchar к типу данных numeric

Я обнаружил странную проблему в своей базе данных, я смог ее исправить, но я не понимаю, ПОЧЕМУ эта ошибка возникла в первую очередь. Я использую Microsoft SQL Server 2017.

Следующий код возвращает ошибку арифметического переполнения:

 SELECT '1000' / 100.0 FROM table_name
 

 SELECT '1000.0' / 100.0 FROM table_name
 

Возвращает Ошибку:

Ошибка арифметического переполнения при преобразовании varchar в тип данных numeric.

Но что странно, так это то, что следующий код НЕ вызывает ошибки:

 SELECT '100' / 100.0 FROM table_name
 

Возвращает: 1.000000 для каждой строки.

 SELECT '999' / 100.0 FROM table_name
 

Возвращает: 9.990000 для каждой строки.

 SELECT '100.0' / 100.0 FROM table_name
 

Возвращает: 1.000000 для каждой строки.

 SELECT '1000' / 100 FROM table_name
 

Возвращает: 10 для каждой строки.

С тех пор я исправил код так, чтобы он использовал преобразование, прежде чем пытаться выполнять арифметику, но что меня беспокоит, так это то, ПОЧЕМУ код работал без преобразования чисел меньше 1000???? Это действительно не дает мне покоя!

У вас есть 2 значения здесь:

1. '1000' что является varchar(4)
2. 100.0 что является decimal(4,1)

В результате при выполнении выражения '1000' / 100.0 varchar a неявно приводится к a decimal , так как decimal имеет более высокий приоритет типа данных. Однако, поскольку самое большое значение decimal(4,1) , которое может быть сохранено 999.9 , затем значение 1000 переполняется, и вы получаете сообщение об ошибке.

While developing data processes in SQL Server, under certain circumstances, you might get the error message: error converting varchar to numeric. This error is similar with the conversion error you might get when you are trying to convert a varchar to float, etc.

Read on to find out the reason for getting this error message and how you can easily resolve it within just a minute.

The Numeric Data Type in SQL Server

Prior to discuss how you can reproduce and resolve the issue, it is important that you first understand the numeric data type in SQL Server. As described in the relevant MS Docs article, the numeric data type has fixed precision and scale, and it has equivalent functionality with the decimal data type.

Arguments

The numeric data type takes two arguments, that is precision and scale. The syntax is numeric(precision, scale).

Precision defines the maximum number of decimal digits (in both sides of the number) and its value range is between 1 and 38.

Scale, defines the number of decimal digit that will be stored to the right of the decimal point. Its value can range between 1 and the value specified for precision.

Here’s an example of a numeric data type value in SQL Server:

DECLARE @numValue NUMERIC(10,2);
SET @numValue=123456.7890

SELECT @numValue as NumValue;
GO

The number returned by the above T-SQL query is: 123456.7890

In the above example I specified as precision 10 and as scale 2.

So, even though I specified 123456.7890 as the numeric value, it was indirectly converted to a numeric(10,2) value and that’s why it returned the value 123456.79

Learn more tips like this! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $12.99

Reproducing the Conversion Error

Great. Now, let’s reproduce the conversion error by trying to convert a “problematic” varchar value to numeric.

You can find this example below:

DECLARE @valueToConvert VARCHAR(50);
SET @valueToConvert='1123,456.7890';

SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber;
GO

When you execute the above T-SQL code, you will get the below exact error message:

Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

How to Resolve the Conversion Error

As you might have observed in the above example, the @valueToConvert variable, besides the dot (.), it also contains a comma (,).

Therefore, at the time of its conversion to the numeric data type, the comma is considered an illegal character for the destination data type (numeric) and that’s why you get the error message.

In order to resolve the conversion error, you just need to remove the comma (,) from the varchar value that you want to convert to numeric.

Note: At this point, you also need to make sure that the varchar value to be converted, is the actual number you wish to convert to the numeric data type. Also, you need to make sure that you only use the decimal symbol, in this case the dot (.), and not any digit grouping symbols, etc.

So, if we remove the comma from the above example, we can see that the conversion is successful.

DECLARE @valueToConvert VARCHAR(50);
SET @valueToConvert='1123456.7890';

SELECT CAST(@valueToConvert AS NUMERIC(10,2)) as ConvertedNumber;
GO

Output:

In general, when converting varchar values to numbers (i.e. decimal, numeric, etc.), you need to be careful in order for your varchar value, not contain any digit grouping symbols (i.e. a comma) or any other characters that do not have a meaning as a number.

Check our Online Courses

• SQL Server 2022: What’s New – New and Enhanced Features
• Data Management for Beginners – Main Principles
• Introduction to Azure Database for MySQL
• Working with Python on Windows and SQL Server Databases
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
• A Guide on How to Start and Monetize a Successful Blog
• Data Management for Beginners – Main Principles

Read Also

Feel free to check our other relevant articles on SQL Server troubleshooting:

• Error converting data type varchar to float
• Rule “Setup account privileges” failed – How to Resolve
• SQL Server 2022: What’s New – New and Enhanced Features (Course Preview)
• SQLServerAgent could not be started (reason: Unable to connect to server ‘(local)’; SQLServerAgent cannot start)
• ORDER BY items must appear in the select list if SELECT DISTINCT is specified
• There is no SQL Server Failover Cluster Available to Join
• There is insufficient system memory in resource pool ‘internal’ to run this query.
• There is not enough space on the disk. (mscorlib)
• A network-related or instance-specific error occurred while establishing a connection to SQL Server
• Introduction to Azure Database for MySQL (Course Preview)
• [Resolved] Operand type clash: int is incompatible with uniqueidentifier
• The OLE DB provider “Microsoft.ACE.OLEDB.12.0” has not been registered – How to Resolve it
• SQL Server replication requires the actual server name to make a connection to the server – How to Resolve it
• Issue Adding Node to a SQL Server Failover Cluster – Greyed Out Service Account – How to Resolve
• Resolve SQL Server CTE Error – Incorrect syntax near ‘)’.
• SQL Server is Terminating Because of Fatal Exception 80000003 – How to Troubleshoot
• An existing History Table cannot be specified with LEDGER=ON – How to Resolve
• Advanced SQL Server Features and Techniques for Experienced DBAs
• SQL Server Database Backup and Recovery Guide
• … all SQL Server troubleshooting articles

Featured Database Productivity Tools

Snippets Generator: Create and modify T-SQL snippets for use in SQL Management Studio, fast, easy and efficiently.

Learn more

Dynamic SQL Generator: Convert static T-SQL code to dynamic and vice versa, easily and fast.

Learn more

Subscribe to our newsletter and stay up to date!

Check out our latest software releases!

Check our eBooks!

Rate this article: (3 votes, average: 4.33 out of 5)

Loading…

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

How to resolve the error: Error converting varchar to numeric in SQL Server

Click to Tweet

Views: 20,337

SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

SQL Server 2008 and Earlier

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

You may be interested to read Erland Sommarskog’s Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

There are additional issues here but I can’t address them now.

P.S. I had a brainstorm today. An alternate to the «traditional way» that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I’ve noticed that since learning CROSS/OUTER APPLY I’ve improved my query capability with older SQL Server versions, too—as I am getting more versatile with the «outer reference» capabilities of SELECT, ON, and WHERE clauses!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It’s a lot shorter than the CASE statement.

I keep getting this error and I dont understand why. I checked all CONVERT functions but not sure where is the issue.

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

This is the query and you can see Line 1 does not have any type of conversion.

SELECT 
  'Entity Code|Department Code|Job Code|Pay Code|Pay Period Code|Employee Code|Fiscal Year|Time 
  Class|Unit Type|Amount|Home Entity Code|Home Department|Pay Period End Date' AS PRL_HEADERR
UNION ALL SELECT       
  (ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DIST_COMPANY, 4))), '')    
  + '|' + ISNULL(RTRIM(LTRIM(RIGHT(REPLICATE(0, 4) + SUB.DST_ACCT_UNIT2, 4))), '')   
  + '|' + ISNULL((SUB.JOB_CODE2), '')                      
  + '|' + ISNULL((SUB.PAY_CODE2), '')                      
  + '|' + ISNULL((SUB2.PAYPERIOD), '')                     
  + '|' + ISNULL(
      (CASE WHEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),1,1)='0' 
      THEN SUBSTRING(RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7),2,LEN(RIGHT(REPLICATE(0, 7) + 
      SUB.EMPLOYEE, 7))) 
      ELSE RIGHT(REPLICATE(0, 7) + SUB.EMPLOYEE, 7) END), '')    
  + '|' +       ISNULL((SUB.FISCAL_YEAR), '')                    
  + '|' +       ISNULL((SUB.TIME_CLASS), '')                     
  + '|' +       ISNULL((SUB.UNIT_TYPE), '')                      
  + '|' +       ISNULL((SUB.UNIT_VALUE), '')                     
  + '|' +       ISNULL(HOME_ENTITY_CODE, '')                     
  + '|' +       ISNULL(HOME_DEPARTMENT, '')                     
  + '|' +     ISNULL(PAY_PER_END_DATE, ''))  AS PRL_HEADER
FROM
 (
  SELECT
     TOP 100 PERCENT DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Hours' AS UNIT_TYPE,
     HOURS AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE 
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  UNION ALL
  SELECT TOP 1000
     DIST_COMPANY,
     LTRIM(RTRIM(DST_ACCT_UNIT)) AS DST_ACCT_UNIT2,
     LTRIM(RTRIM(JOB_CODE)) AS JOB_CODE2,
     LTRIM(RTRIM(PAY_CODE)) AS PAY_CODE2,
     PER_END_DATE,
     EMPLOYEE,
     (CONVERT(VARCHAR(4), GL_DATE, 112)) AS FISCAL_YEAR,
     'A' AS TIME_CLASS,
     'Amount' AS UNIT_TYPE,
     WAGE_AMOUNT AS UNIT_VALUE,
     COMPANY AS HOME_ENTITY_CODE,
     LTRIM(RTRIM(HM_ACCT_UNIT)) AS HOME_DEPARTMENT,
     CONVERT(VARCHAR(8), PER_END_DATE,112) AS PAY_PER_END_DATE 
  FROM
     TEST_TABLE
  WHERE
     (
        GL_DATE >= CAST('20200101' AS DATETIME)
        AND GL_DATE <= CAST('20201231' AS DATETIME)
     )
  ORDER BY
     EMPLOYEE,
     PER_END_DATE 
 )
 AS SUB 
 INNER JOIN
  (
     SELECT
        ROW_NUMBER() OVER (ORDER BY PP_END_DATE) AS ROWNUM,
        SUB3.PP_END_DATE,
        (
           (CONVERT(VARCHAR(4), SUB3.PP_END_DATE, 112) + '-') + ROWNUM
        )
        AS PAYPERIOD
     FROM
        (
           SELECT DISTINCT
              TOP 100 PERCENT PER_END_DATE AS PP_END_DATE 
           FROM
              TEST_TABLE
           WHERE
              (
                 PER_END_DATE >= CAST('20200101' AS DATETIME)
                 AND PER_END_DATE <= CAST('20201231' AS DATETIME) 
              )
           ORDER BY
              PER_END_DATE 
        )
        AS SUB3 
  )
  AS SUB2 
  ON SUB2.PP_END_DATE = SUB.PER_END_DATE

This query was written in Oracle by someone else and I’m converting it to MS SQL. Also the app is large and legacy, written in Cobol. Good thing is there is not a lot of data here therefore performance is not a big concern.

hello friends…

i have following query.

declare @month int
declare @year int
set @month=8
set @year =2014

declare @startdate datetime
declare @enddate datetime
set @startdate=CONVERT(datetime,convert(varchar,@year)+'-'+convert(varchar,@month)+'-'+'01')

if (@month>= MONTH(GETDATE()) and @year>=YEAR(GETDATE()))
begin
	set @enddate=(select GETDATE()) 
end
else
begin
	set @enddate=DATEADD(day,-1,DATEADD(month,1,@startdate))
end

create table #suspended_alumina_gpl
(
	edate datetime,
	avgliq decimal(18,5)
)

while (@startdate<=@enddate)
begin
	
	insert into #suspended_alumina_gpl 
	select convert(datetime,@startdate),isnull(AVG(spent_liquor),'') from suspended_alumina_gpl
	where CONVERT(varchar,edatetime,103)=CONVERT(varchar,@startdate)
		
	set @startdate=DATEADD(day,1,@startdate)
end
select * from #suspended_alumina_gpl

but it throws an error as follows while executing it.

Msg 8114, Level 16, State 5, Line 29
Error converting data type varchar to numeric.

Я обнаружил странную проблему в своей базе данных, я смог ее исправить, но я не понимаю, ПОЧЕМУ эта ошибка возникла в первую очередь. Я использую Microsoft SQL Server 2017.

Следующий код возвращает ошибку арифметического переполнения:

 SELECT '1000' / 100.0 FROM table_name
 

 SELECT '1000.0' / 100.0 FROM table_name
 

Возвращает Ошибку:

Ошибка арифметического переполнения при преобразовании varchar в тип данных numeric.

Но что странно, так это то, что следующий код НЕ вызывает ошибки:

 SELECT '100' / 100.0 FROM table_name
 

Возвращает: 1.000000 для каждой строки.

 SELECT '999' / 100.0 FROM table_name
 

Возвращает: 9.990000 для каждой строки.

 SELECT '100.0' / 100.0 FROM table_name
 

Возвращает: 1.000000 для каждой строки.

 SELECT '1000' / 100 FROM table_name
 

Возвращает: 10 для каждой строки.

С тех пор я исправил код так, чтобы он использовал преобразование, прежде чем пытаться выполнять арифметику, но что меня беспокоит, так это то, ПОЧЕМУ код работал без преобразования чисел меньше 1000???? Это действительно не дает мне покоя!

У вас есть 2 значения здесь:

1. '1000' что является varchar(4)
2. 100.0 что является decimal(4,1)

В результате при выполнении выражения '1000' / 100.0 varchar a неявно приводится к a decimal , так как decimal имеет более высокий приоритет типа данных. Однако, поскольку самое большое значение decimal(4,1) , которое может быть сохранено 999.9 , затем значение 1000 переполняется, и вы получаете сообщение об ошибке.