Cast Varchar to Decimal - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cast Varchar to Decimal

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-04-07 : 05:40:50

Hi

I have one table that has Varchar column, with values like: 0.234 or 8.8 or 0.938773

I would like to insert these values into another table that has a Decimal (18,3) column. I am using following sql:

UPDATE products
SET actualWeight = Cast (weight_kg as decimal(18,3)), VolumetricWeight = Cast ( volumetric as decimal(18,3))
FROM stock_weights sw
WHERE products.productid = sw.stock_ref

I keep getting error message: "Error converting data type varchar to numeric. "

Any idea how I can get SQL command to run?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-07 : 05:44:35

you probablly have data in column weight_kg or volumetric that is not able to convert to decimal
try this to list out the problem data

select  *
from    stock_weights
where   isnumeric(weight_kg) = 0
or      isnumeric(volumetric) = 0

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-04-07 : 05:57:35

Thanks alot
that was the problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-07 : 09:10:31

IsNumeric is not reliable. If you have data like 12,345.45, you have problem with that
Read this
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources