Cannot convert a char value to money

Question

Post reply

Cannot convert a char value to money

ethio6

Mr or Mrs. 500

Points: 595
More actions
February 22, 2008 at 9:54 am

#187003

We have the same databse in three different envionment. The statement below works just fine in two database enviornment, but I am getitng the error in the third one. All the databases are Microsoft SQL Server 2005. I could not pinout the source of the issue. Plsease any input that you might be able to provied. Thanks
The issue in simpletst from
DECLARE @UDF_Value nvarchar(255)
set @UDF_Value = 111
SELECT CONVERT(money, @UDF_Value)
Msg 235, Level 16, State 0, Line 1
Cannot convert a char value to money. The char value has incorrect syntax.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

How big is your largest udf_value and do you ahve any special formatting in the nvarchar return?

My blog: http://jahaines.blogspot.com

ethio6 Mr or Mrs. 500 Points: 595 More actions · Answer 2

Thanks Adam!

The script specifically converts a numeric values which is stored as nvarchar(255). The conversion does not convert other text values, portion of the script filters out texts It is suprsing that the same scripts are working in another two environment with the same value. All the databses are the same version. I did check SELECT @@VERSION

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 3

I run the code and there is nothing wrong with that:

here is the value I get back 111.00

It looks like you have posted this elsewhere and you have got the same responses from the folks out there.

(or at least someone has the exact code and he/she is trying to get some answers)

Here is the deal, you need to run this code in two or more environments, if you have not done so yet, and sit down and analyze the difference between the environments that this code is being run on.

There is nothing wrong with the code, look also into the data file / table that this code is running on.

Make sure that the data is is trimed first before running the function convert on that.

make sense?

good luck.

John Esraelo

Cheers,
John Esraelo

anuragvarma SSC Rookie Points: 39 More actions · Answer 4

Here is the workaround that will work in most cases.

Just so you know it is a cast/conversion issue - nothing wrong with your code. I had a similar problem where I needed to cast a varchar value to money from a huge lookup table. Now I cannot change the column in the lookup table because that column stores variety of data (from strings to dates to numerics etc.). The workaround is to create a quick pseudo/virtual table in the query and use it as follows:

(1) Here is the query that did not work:

SELECT MyAmount = cast(L.LookupValue as money), col1, col2, ...FROM Lookup L WHERE...

(2) Modify the above query as follows and it will work fine:

SELECT MyAmount = cast(L.LookupValue as money), col1, col2, ...FROM (select * from lookup where ISNUMERIC(L.LookupValue) = 1) L WHERE...

You can also create a view instead of using the pseudo/virtual table so you can reuse it in other places too.

Hope this helps.

Anurag Varma