1. Registered User
Join Date
Apr 2007
Posts
63

A QUICK QUESTION.

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

Its throwing -413 error.

2. Registered User
Join Date
Jan 2003
Posts
4,310
Not if the number exceeds 9999999.99

Andy

3. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
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```

4. Registered User
Join Date
May 2007
Location
somewhere in dbforums
Posts
221
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
case (when value < 9999999.99)
then CAST
else
HANDLE ERROR
end

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•