CAST a Decimal

prem18 · #1 (**permalink**) 11-13-08, 13:28

A QUICK QUESTION.

Is it not possible to cast a decimal(12) to decimal(7,2)?

Its throwing -413 error.

ARWinner · #2 (**permalink**) 11-13-08, 13:50

Not if the number exceeds 9999999.99

Andy

tonkuma · #3 (**permalink**) 11-13-08, 14:03

Yes, if the values was less than 100000.

Code:

------------------------------ Commands Entered ------------------------------
SELECT d                        AS "decimal(12)"
     , CAST(d AS decimal(7,2) ) AS "decimal(7,2)"
  FROM (VALUES CAST(99999.9 AS decimal(12)) ) AS d(d)
;
------------------------------------------------------------------------------

decimal(12)    decimal(7,2)
-------------- ------------
        99999.     99999.00

  1 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT d                        AS "decimal(12)"
     , CAST(d AS decimal(7,2) ) AS "decimal(7,2)"
  FROM (VALUES CAST(100000. AS decimal(12)) ) AS d(d)
;
------------------------------------------------------------------------------

decimal(12)    decimal(7,2)
-------------- ------------
SQL0413N  Overflow occurred during numeric data type conversion.  
SQLSTATE=22003

<snipped some lines>

 sqlcode :  -413 

 sqlstate :  22003

nick.ncs · #4 (**permalink**) 11-13-08, 21:08

basically your decimal(12) can hold much more data than decimal(7,2) and since you are narrowing down [ i.e. going from a higher container decimal(12) to a lower sized container decimal(7,2) ] you cannot input anything more than 9999999.99 for then it i'll be something like trying to pour water from a 2 liter jug into a 1 liter jug; obviously there'll be an overflow

one thing you can try out is if value is less than 9999999.99 then convert else handle error

Quote:

case (when value < 9999999.99)
then CAST
else
HANDLE ERROR
end

Internet Services

Web Development

Digital Marketing

Consumer Tech