My table has NVARCHAR(255) column with data that is supposed to be dollar amounts. Like 1, 2, 8, 2.99, 3.99.
I insert this data into another table in a column which has a MONEY datatype. Before I insert, I do as such.
I get an error that I can not convert. The message is "Cannot convert a char value to money. The char value has incorrect syntax."
So what I did was instead of converting to Money, I do CONVERT(float, ColA) and then insert into table with the MONEY data type column.
What could be the potential fallback for this work-around?
asked Apr 14, 2011 at 11:17 AM in Default
The only potential fallback could be because the float should almost never be used in SQL Server. It has a totally different purpose from what is usually expected from a single precision floats of the front ends. In other words, initially converting the character data to decimal(19, 4) before converting it to money (which is internally a hardcoded decimal(19, 4) plus some bells and whistles) could be a safer bet.Oleg
answered Apr 14, 2011 at 11:44 AM
I would only add, that you should check for the value causing the problem, so you do not have wrong data in the final table. The
The only thing actualy coming to my mind is a number in exponential format which is convertible to float, but not to money directly.
SELECT CONVERT(decimal(19,4), '1,234.5')
answered Apr 14, 2011 at 12:03 PM