Cannot convert char to money

[nuVBer]

I am trying to copy some date from one table to another where the first table has a field with varchar values (an example looks like this "1,000.00&quot and I need it to go to a table that has a field that is money type. When I use the convert function I get the error "Cannot convert a char value to money. The char value has incorrect syntax." Anyone know what is wrong here?

 

[karluk]

Most likely you have a few rows where the money value is not formatted correctly. I would check for this situation in your where clause. Something like

insert into new_table
select cast(varchar_column as money) from old_table
where isnumeric(varchar_column) = 1

If necessary you can then write a separate insert to handle the rows with invalid money values.

 

[bperry]

Hi there:
There is a little quirk in SQL Server: Most numeric types will automatically convert from character strings during Inserts and Updates. For some reason, though, money (and I think smallmoney) will not. However, even though strings won't convert to money automatically, we can do it explicitly. This should work fine for you:

insert testttable values (2,CAST('12.23' as Money))

Apart from that known problem, what Karluk mentioned may also be true: watch out for a string column that has characters that will not convert to numbers.

--------------------
bperry

 

[nuVBer]

Well, I still can't get it to work. Would the fact that the varchar values have commas in them matter? The values are of the form "x,xxx.xx". Do the quotes have anything to do with it? I'm stuck!!!

 

[SQLBill]

I'm guessing here, but when you convert a value (in this case a varchar) it has to convert everything in that field. That means that the convert you are using is trying to convert the quotes into datatype money also. Can you test it by writing a script that removes the quotes and then converts the field? If they are required for your report, you can always have the script put them back in after the conversion.

That's my thoughts on this....I'm trying to come up with a script that does what I'm suggesting.

-SQLBill

 

[bperry]

Hmmm. very interesting.
I have a test table that has 1 column, which is a money datatype.
All these tests below work perfectly; see how I have different variations with commas and spaces.

insert testtable values (CAST('1,000.23' as Money))
insert testtable values (CAST('1000.23' as Money))
insert testtable values (CAST(' 1000.23' as Money))
insert testtable values (CAST(' 1,000.23 ' as Money))

This also works perfectly:
declare @mymoney char(10)
set @mymoney = ' 1,001.00'
insert testtable values (CAST(@mymoney as Money))

Perhaps you want to try at least that on your system, to see if you can at least confirm that that works.

After that, what about Karluk's suggestion: could you have any rows that actually have 'bad data': characters that cannot convert to numbers at all?

 

[nuVBer]

I've tried all of the above..the last time I tried the expression:

(CAST(SUBSTRING(WithAlloc_88, 2,Len(WithAlloc_88) - 2) as Money))

where WithAlloc88 is the varchar datatype column I'm trying to copy into the new table. Unfortunately, I get the same error..."Cannot convert a char value to money. The char value has incorrect syntax."

I've just about given up. I've spent the last 2 days trying to get this thing to work. I've got to abort and try something completely different I suppose. Thanks guys (bperry and SQLBill)

 

[bperry]

I would next do this:

Select
SUBSTRING(WithAlloc_88, 2,Len(WithAlloc_88) - 2)
as NewMoney
From MyTable

to see what the values look like just before they are Cast into Money. (I'm thinking they don't like you think they do)

 

[karluk]

Bperry is right. You need to do more investigation to find out which rows are failing to convert. However instead of just visually scanning your data I would use the isnumeric function. That should give you all the rows that can't be converted

Select WithAlloc_88 From MyTable
where isnumeric(WithAlloc_88) = 0

 

[nuVBer]

Those suggestions work fine and produce the results I would expect...good clean values. I have chosen a different path though using a DTS package that uses activeX script to convert the values (FormatNumber(CDbl(WithAlloc_88),0) that works just fine and is accepted into the new table.

Thanks for all your help.

 

[Guest_imported]

How can I perform a price range search on a money datatype field and have accurate results ?

 

[chiph]

Use the Between operator:

SELECT ItemName
WHERE ItemPrice BETWEEN 2.00 AND 8.99

Chip H.