asked
Conversion, Char, Float, Money Game
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. Convert(money, ColA) 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?
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
@Matt Whitfield What I meant to say is this: the **float** and **real** in T-SQL are not the same (though they can be IF the float is float(24)) and also that float in T-SQL does not mean what the float means in C#. Very seldom one can observe anybody ever declaring the var as float(24) as instead people use something like **declare @f as float;** meaning **declare @f as float(53);**. In this case, the values range from **-1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308**, need 8 bytes of storage but still allow a total of 15 digits in case of (53) and 7 digits in case of (24). C# float on the other hand ranges from **-3.40282347E+38 to 3.40282347E+38** and needs 4 bytes of storage. This hints that default declaration of a var as float in T-SQL does not map to float in C# though it maps to a double quite nicely and declaration of a var as real maps to float in C# but the grand total, with the groceries, of the digits is 7. Conversion from char to money should not be done via float in the middle. Choosing the decimal with appropriate **p** and **s** just seems to be a better option, and it agrees with Joe Celko opinion, whom I and I am sure many others hugely respect :)
@Oleg - can you expand on it a bit? Because I'm interested in what your experiences of double / float and single / real are...
@Matt Whitfield. Primarily because per [BOL][1] Float and Real are:
Approximate-number data types for use with floating point numeric data... Not all values in the data type range can be represented exactly.I really like what Joe Celko said about these in one of his stairway to database design articles published on SSC (abridged to fit in the comment):
Avoid FLOAT and REAL in T-SQL. The problem is that FLOAT needs special handling to avoid rounding errors and do to comparisons. There is a very reason that they are called aproximate numeric data types. This special handling has to be either built into the software or (better) part of the hardware, which means you need a floating point processor. But even if the chips were cheap, you cannot reasonably expect T-SQL to do that kind of mathematical optimization. SQL is a data retrieval and management language and not meant for computations. If you need decimal places, then use DECIMAL(s,p) data types. They work just fine and they port. The trick is to give yourself enough decimal places to get correct rounding[1]: http://msdn.microsoft.com/en-us/library/ms173773.aspx
@Oleg - Ok - but the bit I don't understand is where you say 'totally different purpose from what is usually expected from single precision floats of the front ends'. AFAIK (the K being knew rather than know) the SQL float/real map reasonably to the .NET double/float types. So, if you understand what you're working with, and you're not representing something that needs to be absolutely precise, then you should be OK... ?
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 `nvarchar(255)` should be normaly convertible to the money data type if it contains correct numeric values. The only thing actualy coming to my mind is a number in exponential format which is convertible to float, but not to money directly. --convertible SELECT CONVERT(money, CONVERT(float,'12e3')) --not convertible SELECT CONVERT(money, '12e3') --not convertible SELECT CONVERT(money, CONVERT(DECIMAL(19,4),'12e3')) --convertible SELECT CONVERT(money, '1,234.5') --not convertible SELECT CONVERT(float, '1,234.5') --not convertible SELECT CONVERT(decimal(19,4), '1,234.5')
Pavel, great point. I am aware of this because I had been converting the NVARCHARs to MONEY. But on one import, it suddenly failed and wouldnt move forward. I had about 301k values and I ran a ISNUMERIC on them. All turned out to be true. I was just baffled so started using work-arounds.
