0

I have 2 numeric type variable. I want to devide this variables. But I want result is Numeric(5,2).

For example:

DECLARE @DISCOUNT_PRICE numeric(9,2) = '436.63' 
DECLARE @PRICE numeric(9,2) = '1.2'

SELECT CAST((((@DISCOUNT_PRICE*100)/@PRICE) -100 ) AS NUMERIC(5,2))

Variables (9,2) I want result as (5,2)

When I ran this code, I get an error:

Arithmetic overflow error converting numeric to data type numeric

Thanks for the help.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
PsyGnosis
  • 1,489
  • 6
  • 21
  • 28
  • 1
    See [Arithmetic overflow error converting numeric to data type numeric](https://stackoverflow.com/questions/4670903/arithmetic-overflow-error-converting-numeric-to-data-type-numeric) – huMpty duMpty Aug 19 '19 at 08:43
  • The result of that query is `36285.833333333333`, how do you fit that into a `decimal(5,2)`? The largest number that can hold is `999.99`. – Thom A Aug 19 '19 at 08:44
  • I know the problem but. Is there a way to cast numeric(9,2) to numeric(5,2) – PsyGnosis Aug 19 '19 at 08:45
  • Yes, by having a number that **fits**. `DECLARE @n numeric(9,2) = 123.25; SELECT CONVERT(numeric(5,2),@n);` works fine. – Thom A Aug 19 '19 at 08:48
  • `36285.83~` is too large for a `numeric(5,2)`. You can't push a circular block through a smaller circular hole; you have to make to hole bigger or the block smaller. So you either need to make the size of the datatype bigger (the hole), or make the number (the block) smaller to fit. – Thom A Aug 19 '19 at 08:51
  • You can get rid of after 2 decimal (without rounding). doesnt matter. Is there a way ? @Larnu – PsyGnosis Aug 19 '19 at 08:58
  • 1
    Is there a way to what? `36285.83` will **never** fit into a `numeric(5,2)`. It's `35285.84` too large (as I said, the largest value you can fit is `999.99`). The smallest size numeric you could use to fit `36285.83` would be `numeric(7,2)`. If you don't care about the decimal points, you could use a `numeric(5,0)`. – Thom A Aug 19 '19 at 09:02
  • i am having a big enlightenment right now. all these years :DD i thought that the first number was length of before dot (.) (7,2) works thank you! – PsyGnosis Aug 19 '19 at 10:25

0 Answers0