x

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?

more ▼

asked Apr 14, 2011 at 11:17 AM in Default

Slick84 gravatar image

Slick84
1.3k 75 102 142

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Apr 14, 2011 at 11:44 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

Great, Thanks. I'll change it to that and see if the insert still works.
Apr 14, 2011 at 11:47 AM Slick84
@Oleg - can you expand on it a bit? Because I'm interested in what your experiences of double / float and single / real are...
Apr 14, 2011 at 01:51 PM Matt Whitfield ♦♦

@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
Apr 14, 2011 at 02:16 PM Oleg
@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... ?
Apr 14, 2011 at 03:27 PM Matt Whitfield ♦♦

@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 :)
Apr 15, 2011 at 08:10 AM Oleg
(comments are locked)
10|1200 characters needed characters left

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')
more ▼

answered Apr 14, 2011 at 12:03 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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.
Apr 14, 2011 at 12:06 PM Slick84
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x292
x46
x29
x11

asked: Apr 14, 2011 at 11:17 AM

Seen: 3290 times

Last Updated: Apr 14, 2011 at 11:17 AM