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.
| Author |
Topic |
|
TimR
Starting Member
9 Posts |
 Posted - 2012-02-09 : 15:32:08
|
| I'm having trouble converting a varchar field to money. I've tried cast and convert and they appear successful but when I try to run a query on the data (i.e. select field from table where field is > 1) I get an error - Conversion failed when converting the varchar value ' 0.00' to data type int. What am I missing? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-09 : 15:47:53
|
SELECT CONVERT (int, LTRIM(RTRIM(yourField)) FROM yourTableThis will solve the error you showed us. You might have other bad data in there.By the way, you said "money" but your error message is "int" which means you converted to int.select convert(int, ' 0.00')Conversion failed when converting the varchar value ' 0.00' to data type int.select convert(money, ' 0.00')0.00 |
 |
|
|
TimR
Starting Member
9 Posts |
Posted - 2012-02-09 : 15:54:57
|
| Thanks Russell - I get - Conversion failed when converting the varchar value '0.00' to data type int. - when I run your Convert. Bad data is a definite possibility! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-09 : 21:28:14
|
Notice the leading space.You really shouldn't be storing numeric values in character type columns, but it's understood that sometimes this is out of your control.If it is within your control...fix it  |
|
|
|
TimR
Starting Member
9 Posts |
Posted - 2012-02-10 : 04:33:16
|
| Yeah, I tried everything I could think of but couldn't get rid of the leading spaces. Ended up exporting then re-importing the data, that did the trick. Thanks again! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2012-02-10 : 15:34:12
|
| LTRIM() |
|
|
|
|
|
|
Converting varchar to money