convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/