Arithmetic overflow error converting numeric to data type varchar

Question

So, here's my query:

SELECT '$'
       + CONVERT(VARCHAR (6), Cast(Avg(TotalPrice) AS NUMERIC (6, 2))) AS 'Average Price',
       '$'
       + CONVERT(VARCHAR (6), Cast(Min(TotalPrice) AS NUMERIC (6, 2))) AS 'Minimum Price',
       '$'
       + CONVERT(VARCHAR (6), Cast(Max(TotalPrice) AS NUMERIC (6, 2))) AS 'Maximum Price'
FROM   Invoice;

The AVG column and the MIN column work fine but the MAX column returns:

"Arithmetic overflow error converting numeric to data type varchar"

And I'm not sure why I get the error.

radar · Accepted Answer · 2014-11-04 03:51:24Z

2

The NUMERIC(6,2) indicates total 6 digits and out of which 2 are decimal places.

you have a value like 1234.66 then total characters needed is 7

Get the maximum value and use appropriate varchar size, here you need atleast varchar(7)

answered Nov 4, 2014 at 3:51

radar

13.4k2 gold badges27 silver badges33 bronze badges

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

Comments

Pரதீப் · Accepted Answer · 2014-11-04 03:48:59Z

1

Try this

SELECT '$'
       + CONVERT(VARCHAR (10), Cast(Avg(TotalPrice) AS NUMERIC (8, 2))) AS 'Average Price',
       '$'
       + CONVERT(VARCHAR (10), Cast(Min(TotalPrice) AS NUMERIC (8, 2))) AS 'Minimum Price',
       '$'
       + CONVERT(VARCHAR (10), Cast(Max(TotalPrice) AS NUMERIC (8, 2))) AS 'Maximum Price'
FROM   Invoice;

answered Nov 4, 2014 at 3:48

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

3 Comments

DeanOC Over a year ago

Why does the OP need to change the NUMERIC declaration from (6,2) to (8,2)?

Pரதீப் Over a year ago

Beacuse in NUMERIC (6, 2) (precision = 6) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. so precision has to increased.

DeanOC Over a year ago

There isn't any indication that MAX(TotalPrice) is exceeding NUMERIC(6,2).

DeanOC · Accepted Answer · 2014-11-04 03:51:18Z

1

Your problem is that a 'Numeric(6,2)' has up to 6 digits, plus a decimal point (or comma depending on where you are). So you will need to have 'VARCHAR(7)' instead of 6 to cater for this.

answered Nov 4, 2014 at 3:51

DeanOC

7,3326 gold badges44 silver badges57 bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error converting numeric to data type varchar

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related