converting (casting) from decimal(24,4) to decimal(21,4) data type problem

Hello!

I would like to cast (convert) data type decimal(24,4) to

decimal(21,4). I could not do this using standard casting function

CAST(@.variable as decimal(21,4)) or CONVERT(decimal(21,4),@.variable)

because of the following error: "Arithmetic overflow error converting

numeric to data type numeric." Is that because of possible loss of the

value?

Thanks for giving me any advice,

Ziga

What was the value? For a value that fits in both, you should have no issue:

declare @.value decimal(24,4)
set @.value = 10.12

select cast(@.value as decimal(21,4))
go

But if the non-fractional value is too large for the 21,4 datatype, it will go boom:

declare @.value decimal(24,4)
set @.value = 12345678901234567890.1234

select @.value

select cast(@.value as decimal(21,4))

12345678901234567890.1234

Msg 8115, Level 16, State 8, Line 6
Arithmetic overflow error converting numeric to data type numeric.

If this isn't the case, then post the code that fails, the value, and the results of:

select @.@.version

I tried this on the following versions:

Microsoft SQL Server 2000 - 8.00.679 (Intel X86)
Aug 26 2002 15:09:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

|||Yes the non fraction value is to large... So there is no way to solve this problem?|||What is the result that you expect when you try to cast a larger value? CAST will throw error for overflow and that is the only expected behavior. If you want to fit larger values into smaller data type then you need to truncate the value yourself or perform other logic. You could use CASE expression to check for the larger values and conditionally perform cast. Also, take a look at ROUND function. You could use it instead of CAST for the larger values or for the whole conversion.|||

Thanks!

Actually I am performing some mappings between two systems. Interface for the destination system has specification of the field as decimal(21,4). In source system the (calculated) value is larger - decimal(24,4). For those large values where the cast (to decimal(21,4)) is not possible (arithemtic overflow) performing correct mapping is just not possible...

Thakns a lot,

Ziga

|||You still haven't answered how you would like to handle the larger values. Do you simply throw those away? What would it mean to store a truncated result in the database? And if you use it later then you are going to make wrong assumptions. It seems like your table schema is wrong and if you want to retain the higher precision values you need to modify the schema to match the source or vice versa. Otherwise, you will have to use round or truncate the value yourself before inserting and you cannot use CAST.