Arithmetic overflow error converting nvarchar to data type numeric

October 13, 2012 at 10:58 am

#265630

hello

when i run the following sql query:

«update table set price = price * 1.1 «

i get the following error :

«Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting nvarchar to data type numeric.

The statement has been terminated.»

the table is set to nvarchar, and i am just trying to make the prices go up 10%

any ideas?

thanks!

Gopi S

Ten Centuries

Points: 1091

Hi,

In the expression [price * 1.1], SQL Server tries to implicit convert price into numeric(2,1) because 1.1 is type numeric(2,1) which has higher precedence than nvarchar. I think your table should have values greater that 10.

say 10.1, 10.2…… where the precision value is exceeding 2.

To resolve this try

update table set price = price * cast(1.1 as numeric(10,4))

Note: If your price still has more that 10 precision value then change it accordingly.

Thanks

Gopi

duro654321

Mr or Mrs. 500

Points: 572

the value of the price field is:

.05

.25

.6

1

1.25

150.7

250

265.75

etc

so if there are no items price that are greater than $1000 then i would use the following: ?

update field set price = price * cast(1.1 as numeric(1000,7))

Gopi S

Ten Centuries

Points: 1091

no. ‘1000’ is precision 4. so you can have numeric(4,0). ie 4 decimal values in the left of decimal point and not numeric(1000,7)

Ref: http://msdn.microsoft.com/en-us/library/ms187746.aspx

You can understand what is numeric data type and what is precision and scale number

duro654321

Mr or Mrs. 500

Points: 572

i ran «update table set price = price * cast(1.1 as numeric(10,4))» and that worked pretty well

thanks! 🙂

TomThomson

SSC Guru

Points: 104773

Yes, it’s the implicit conversion that’s going wrong because the default type for 1.2 is numeric(2,1). But there’s another problem: if you try to increase a price like 10.37 by 10%, just multiplying by 1.1 doesn’t work because it will deliver 11.387 which may not be a valid currency amount (eg if the currency is euro the smallest value is .01, so .007 is invalid) and you have to decide what rounding you want to do in this case — perhaps rounding to nearest valid number (and what ever you want to do to round something ending in .xy5) or perhaps rounding down or perhaps some other rule.

So I think I would go for something like

update table

set price = convert(nvarchar(36), cast(cast(price as numeric(35,2))*1.1 as numeric(35,2)))

(the inner cast should solve the error; the outer cast does simple default rounding, so replace it with something that does whatever sort or rounding you need if the default isn’t what you want)

Of course if you have a currency unit that splits into 1000 parts instead of 100, you need to use scale 3 instead of scale 2, and so on for other splits. If you will sometimes want to multiply by something more complicated than 1.1 (for example by 1.0625, for a 6¼% increase) you need to reduce the precision from 35 to avoid the possibility of the multiply operation doing some rounding instead of whatever rounding you have chosen to do — pushing it down to 32 isn’t going to put much of a constraint on your prices, unless you have prices of 1000000000000000000000000000000.00 currency units or more. In fact you could just look and see what your nvarchar price column is declared as: if it’s nvarchar(N) you should use precision N-1, unless whoever specified it was being sloppy.

Tom

duro654321

Mr or Mrs. 500

Points: 572

thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

that worked, it cleaned up the data from 5.50000000 to 5.5

but now when i run «select top 10 * from table order by price «

it shows me

x 9.9

x 9.9

instead of 10 +

??

any ideas?

duro654321

Mr or Mrs. 500

Points: 572

when i run

select * from table order by price asc

i get…

115.50

36.00

4.95

4.95

4.95

47.30

49.50

49.50

53.90

when i should get

4.95

4.95

4.95

36.00

47.30

49.50

49.50

53.90

115.50

what do i need to change in the sql query ?

Gopi S

Ten Centuries

Points: 1091

You are trying to order the varchar column and not numeric though the value looks numeric.

Try

—Query 1

select

price

from table

order by CAST(price as numeric(10,4)) asc

—Query 2

select price from (

select CAST(price as numeric(10,4))

price

from table) T

order by price asc

Again this is just hint. Your cast function should have appropriate precision and scale based on previous post

Sean Lange

SSC Guru

Points: 286562

Or even better, change the column type so you don’t have to continually fight the fact that you are storing numbers in a character column.

Don Bernstein

Old Hand

Points: 393

Thanks for this post — it helped me solve a similar problem.

But, wouldn’t it be more clear to use:

update table set price = cast(price as numeric(10,4)) * 1.1

Содержание

1. Arithmetic overflow error converting nvarchar to data type numeric.
2. Ошибка SQL: Arithmetic overflow error converting numeric to data type numeric
3. Специальные предложения
4. См. также
5. Реструктуризация базы в 1С: для чего требуется и о назначении в целом
6. Копии баз данных и размер БД. Проблемы и пути решения
7. Работа с файлом *.dt формата
8. Регистрация в центре лицензирования не выполнена
9. Настройка отказоустойчивого кластера 1C + PostgreSQL (etcd+patroni+haproxy) на Centos 8
10. Workaround me в 1С/MS SQL и не только, системный подход к созданию костылей
11. Ошибка Dump в 1С
12. Режимы запуска системы 1С:Предприятие
13. Оптимизация высоконагруженных конфигураций: история маленькой победы, или советы тем, кто столкнулся с проблемой впервые и не знает, что делать
14. Ошибка формата потока расширения
15. Пропадающие файлы на томе в 1С: КА 2.5
16. Ошибка загрузки большого архива 1Cv8.dt в PostgresSQL на платформе 1С 8.3.19
17. SAMBA для 1С
18. Регламентное задание по завершению сеансов пользователей 1С
19. Базовые приемы работы с кластером 1С при помощи БСП

This error comes when you try to store an out-of-range floating point value into a numeric variable. For example, if your NUMERIC or DECIMAL variable is defined as NUMERIC(5,2) then the maximum value it can hold is 999.99, if you try to store something like 1000.00 then it will throw «Arithmetic overflow error converting numeric to data type numeric». One of the common reasons for this error is the ignorance and misunderstanding of the NUMERIC data type. For example, many SQL Server DBAs and developers think that a NUMERIC(5,2) variable can hold a 7 digit floating point number where 5 digits are before the decimal and 2 digits are after the decimal. This is wrong.

A NUMERIC(5,2) means, the total number of digits in the value cannot exceed 5 and decimal precision is 2 digits i.e. the maximum possible value is 999.99.

Another thing SQL programmers don’t know and remember about NUMERIC or DECIMAL data types in Microsoft SQL Server is that it doesn’t throw this error if you specify more digits than permitted after the decimal point, instead, it does rounding for example if you store 100.999 then it will store 101.00 after rounding.

Here is an SQL query to prove these points:

DECLARE @sample NUMERIC(5,2)
SET @sample = 1000.554
SELECT @sample

Output
Arithmetic overflow error converting numeric to data type numeric.

Explanation: 

This time SQL Server throws the error because we are trying to store 1000 but the maximum value a NUMERIC(5,2) can hold is 999 before the decimal point. You need to increase the width of the variable to store this number e.g. making @sample NUMERIC(6,2) will solve this error as shown below.

Arithmetic overflow error converting numeric to data type numeric in SQL Server Examples

Here are some more SQL queries which will confirm the behavior of the NUMERIC variable and its range:

DECLARE @sample NUMERIC(5,2)
SET @sample = 100.554 // no rounding because the extra digit is less than 5
SELECT @sample AS Result

Result
100.55

DECLARE @sample NUMERIC(5,2)
SET @sample = 100.555 // rounding will happen
SELECT @sample AS Result

Result
100.56


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.55 // no rounding because value is under defined precision
SELECT @sample AS Result

Result
100.55


DECLARE @sample NUMERIC(5,2)
SET @sample = 100.999 // Rounding to nearest value
SELECT @sample AS Result

Result
101.00

DECLARE @sample NUMERIC(5,2)
SET @sample = 999.999 // error because after rounding value will be
                      // out-of-range for defined numeric type
SELECT @sample AS Result

Result
Arithmetic overflow error converting numeric to data type numeric.

That’s all about «Arithmetic overflow error converting numeric to data type numeric in SQL Server». You can see that cause of the error is usually out-of-range value for the defined NUMERIC type. Just check the source of value and correct or increase the precision level of your column.

Always remember that NUMERIC(5,2) means a total of 5 digits with 2 digits after the decimal point, and the maximum value it can hold is 999.99. Beware of rounding due to more additional digits after the decimal point, which can also cause «Arithmetic overflow error converting numeric to data type numeric» in Microsoft SQL Server.

Related SQL Server articles you may like to explore

Thanks for reading this article. If you like the information given here and my explanation then please share it with your friends and colleagues. 

  [PDOException]
  SQLSTATE[22003]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Arithmetic overflow error converting nvarchar to data type numeric.

        // options for escalation index additional columns
        $rateOptions = array(
            'scale'     => 13,
            'precision' => 13,
        );

        // drop current table
        $this->dropTable('ams_escalation_index');

        // retrieve and empty table
        $table = $this->table('ams_escalation_index', array(
            'id'    => 'escalation_index_id',
        ));

        // add new columns and update table structure
        $table->addColumn('escalation_month',   'integer');
        $table->addColumn('escalation_year',    'integer');
        $table->addColumn('escalation_index',   'decimal', $rateOptions);
        $table->addColumn('labour',             'decimal',  $rateOptions);
        $table->addColumn('plant',              'decimal',  $rateOptions);
        $table->addColumn('materials',          'decimal',  $rateOptions);
        $table->addColumn('fuel',               'decimal',  $rateOptions);
        $table->create();

        // add table data
        global $Paths;
        $fh = fopen(dirname(__FILE__) . '/escalation_index_migration/escalation_indexes.csv', 'r');
        // csv field order
        $fields = array(
            'escalation_month',
            'escalation_year',
            'labour',
            'plant',
            'materials',
            'fuel',
            'escalation_index',
        );
        $i = 1;
        while ( $row = fgetcsv($fh) ) {

            // convert row values to nominal values
            $row[0] = intval(date('n', strtotime($row[0])));
            $row[1] = intval($row[1]);
            $row[2] = floatval($row[2]);
            $row[3] = floatval($row[3]);
            $row[4] = floatval($row[4]);
            $row[5] = floatval($row[5]);
            $row[6] = floatval($row[6]);

            // prepare data and insert
            $data = array_combine($fields, $row);
            $data['escalation_index_id'] = $i;
            $table->insert(array_combine($fields, $row));
            $i++;
        }
        $table->save();

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(varchar,TIMESTAMP,121) as ]TimeStamp],
‘TEST’
FROM A

oops, you may want to convert(nvarchar instead of just varchar.  In theory either would work though.

also I got my bracket turned around, should be:

    as [TimeStamp]

actually, the alias is not really needed, so:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP,121),
‘TEST’
FROM A

@KnightEknight:

I am trying this query

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,TIMESTAMP,121))),
‘TEST’
FROM A

Still same error.

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(nvarchar,TIMESTAMP,121),
‘TEST’
FROM A

Still same error.

sorry, try this:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime,121))),
‘TEST’
FROM A

grr!  I think I left out a closing paren around the cast function — should be:

ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime),121))),

I got Incorrect syntax near ‘,’.

@ line…ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime,121))),

ltrim(rtrim(convert(nvarchar,cast([TIMESTAMP] as datetime),121))),

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

hmm, now that works for me.  Try this and tell me the result you get:

declare @ts timestamp
select  @ts = convert(timestamp,GETDATE())  — initialize @ts to a timestamp
select CONVERT(nvarchar,cast(@ts as datetime),121)

wow .. I got the results….
how to use this my query?

I mean, I got today’s date

results:

2011-04-25 14:29:01.223

TIMESTAMP is the name of your column, right?

… and it is also of type «timestamp» ?

typo error: my column is TransTimeStamp datatype nvarchar(27), my bad

I understood that you were not using the real table/column names, so no worries — I just wanted to make sure about the types.  The data type of TransTimeStamp on table B is nvarchar, but what is it on table A — timestamp or datetime?

or are you just trying to get the current date and time into B?

declare @ts TRANSTIMESTAMP
select  @ts = convert(TRANSTIMESTAMP,GETDATE())  — initialize @ts to a timestamp
select CONVERT(nvarchar,cast(@ts as datetime),121) from IMPORT_WMFUNT

Msg 2715, Level 16, State 3, Line 3
Column, parameter, or variable #1: Cannot find data type TRANSTIMESTAMP.
Parameter or variable ‘@ts’ has an invalid data type.

Yes, TIMESTAMP is a valid data type, as is DATETIME … I would expect TransTimeStamp to be your column name, but I need to know what its type is.

are you just trying to get the current date and time into B?  If so, then:

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
ltrim(rtrim(convert(nvarchar,getdate(),121))),
‘TEST’
FROM A

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
TransTimeStamp,
‘TEST’
FROM A

DataTypes:

Table.A

TransTimeStamp: nvarchar(27)

Table.B

lastUpdated : DateTime

INSERT INTO B
(
LastUpdated,
LastUpdatedBy
)
SELECT
convert(datetime,ltrim(rtrim(TransTimeStamp))),
‘TEST’
FROM A

now, the above assumes that TransTimeStamp has values in it that are properly formatted dates, like ’04/25/2011′ or ‘2011-04-25 03:14:01’

2010-10-18-09.34.14.000000 is nvarchar value in Table A.

I got the same error Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

So I ran this query

select CONVERT(nvarchar,cast([TRANSTIMESTAMP] as datetime),121) from A

Still the same error

select convert(datetime,ltrim(rtrim(TransTimeStamp))) from A

same error

stand by, i got this now…

The date format of that column is non-standard, so first we have to transform it into a standard date, which is why this query is so ugly, but (hopefully) it will work:

INSERT INTO B
(
 LastUpdated,
 LastUpdatedBy
)
SELECT
convert(datetime,substring(TransTimeStamp,1,10)+’ ‘+substring(replace(TransTimeStamp,’.’,’:’),12,11)),
‘TEST’
FROM A

Still the same error:

so I ran

select convert(datetime,substring(TransTimeStamp,1,10)+’ ‘+substring(replace(TransTimeStamp,’.’,’:’),12,11))
 from A

Still same error.

okay, let’s break it down … run these separately:

select substring(TransTimeStamp,1,10)
from A

select substring(replace(TransTimeStamp,’.’,’:’),12,11)
from A

When I tried to convert the datatype, by right click and modify:

‘A’ table
— Warning: Data might be lost converting column ‘TransTimestamp’ from ‘nvarchar(27)’.

I clicked Yes

Got the following message

‘A’ table
— Unable to modify table.  
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

I don’t expect that will work anyway because the format of the text in that column is not a standardized date format.

Please post the results of the 2 queries above.

I mean these two:

select substring(TransTimeStamp,1,10)
from A

select substring(replace(TransTimeStamp,’.’,’:’),12,11)
from A

Query #1:

select substring(TransTimeStamp,1,10)
from A

Results:

 2010-10-1
 2010-10-1
 2010-10-1
 2010-10-1

Query #2:

select substring(replace(TransTimeStamp,’.’,’:’),12,11)
from A

Results:

-09:34:14:0
-09:34:29:0
-09:34:45:0
-09:35:02:0

Query #3:

select TransTimeStamp from A

Results:

 2010-10-18-09.34.14.000000
 2010-10-18-09.34.29.000000
 2010-10-18-09.34.45.000000
 2010-10-18-09.35.02.000000

well, it doesn’t look like that on my system, but maybe we can correct for it like this … please post the results:

select substring(TransTimeStamp,1,11)
from A

select substring(replace(TransTimeStamp,’.’,’:’),13,8)
from A

aha!  I see the problem — there is a leading space on those timestamps, correct?

… that’s why you were doing the ltrim / rtrim thing … let’s try this (only ltrim is necessary):

select substring(ltrim(TransTimeStamp),1,10)
from A

select substring(replace(ltrim(TransTimeStamp),’.’,’:’),12,11)
from A

— and if that works, then hopefully this will too:

INSERT INTO B
(
 LastUpdated,
 LastUpdatedBy
)
SELECT
convert(datetime,substring(ltrim(TransTimeStamp),1,10)+’ ‘+substring(replace(ltrim(TransTimeStamp),’.’,’:’),12,11)),
‘TEST’
FROM A

yes.

(Query 1)
select substring(TransTimeStamp,1,11)
from A
Results:
 2010-10-18
 2010-10-18
 2010-10-18
 2010-10-18

(Query 1)
select substring(replace(TransTimeStamp,’.’,’:’),13,8)
from A

Results
09:34:14:000000
09:34:29:000000
09:34:45:000000

Query 3

Results:
 2010-10-18-09.34.14.000000
 2010-10-18-09.34.29.000000
 2010-10-18-09.34.45.000000
 2010-10-18-09.35.02.000000

How about these?

select substring(ltrim(TransTimeStamp),1,10)
from A

select substring(replace(ltrim(TransTimeStamp),’.’,’:’),12,11)
from A

select substring(ltrim(TransTimeStamp),1,10)
from A

2010-10-18
2010-10-18
2010-10-18
2010-10-18

select substring(replace(ltrim(TransTimeStamp),’.’,’:’),12,11)
from A
09:34:14:00
09:34:29:00
09:34:45:00
09:35:02:00

View this solution by signing up for a free trial.

WOW…you are amazing….finally it worked.

Thank you very much for the efforts. You made my day.

Thank you very much.

• Remove From My Forums

• Question

• Hi

  I am having a strange issue selecting data from an indexed view… I am using SQL Server 2008 R2.

  I have created an indexed view with two columns — ‘DataValue’ and ‘EventFk’. ‘DataValue’ is nvarchar(max) and ‘EventFk’ is integer in the underlying tables.

  ‘DataValue’ holds call number details in the format ‘123456.0’, which I have converted to integer in the view, so that I can (a) index the view and (b) join this view to a table in another database, i.e.
  
  cast(cast(DataValue as decimal) as int) as ‘CallNo’

  I have successfully indexed the view, however I am having problems when it comes to actually selecting individual values from the view itself — for example:
  select * from dbo.vwMapToCLEOSSRSNEW where CallNo = 764266

  is returning the following error:
  Error converting data type nvarchar to numeric.

  However: select * from dbo.vwMapToCLEOSSRSNEW
  works perfectly.

  I am 99.9% certain that the view is not returning any nvarchar values, so I am uncertain as to why I would be receiving this error.  It’s as if my select statement is trying to retrieve the value 764266 from the views’ underlying tables (which DO contain
  nvarchar values), rather than the view itself.

  
  I have posted my full code below, any help is appreciated!

  Thanks

  —Create the view
  SET ANSI_NULLS ON
  GO

  SET QUOTED_IDENTIFIER ON
  GO

  CREATE VIEW [dbo].[vwMapToCLEOSSRSNEW] WITH SCHEMABINDING
  AS

  SELECT /*TOP 99 PERCENT*/ cast(cast(DataValue as decimal) as int) as ‘CallNo’,EventFk
  FROM dbo.[EventData] a
  INNER JOIN dbo.DataItem b ON a.EventDataId =b.EventDataFk
  WHERE b.[EventDataFK] = a.EventDataID
  AND DataKey = N’AuditCallRef’

  GO

  —create index
  create unique clustered index ucidx_CallNo_EventFk on dbo.vwMapToCLEOSSRSNEW(CallNo,EventFk)