Cast Varchar to Float Resulting In (Scientific) E Notation

Question

Post reply

Cast Varchar to Float Resulting In (Scientific) E Notation

Viewing 8 posts - 16 through 23 (of 23 total)

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

waxingsatirical SSC Eights! Points: 934 More actions · Answer 1

Ok, I must admit, I've not seen such am alarming example of float mathematics going wrong before. This is kinda scary.

While I have your attention though... how do you get around the decimal rounding problem:

declare @a decimal (38,28) = 1.123456789

declare @b decimal (38,28) = 1.123456789

declare @c decimal (38,28) = 1.123456789

select @a * @b * @c

?

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

Start by identifying how many digits of precision you need.

Because, even if we had infinite precision calculations, when displaying the data back to users, we're not going to be displaying 28 decimal places (unless you're doing sub-atomic physics maybe), so we will be rounding at some point for display

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Jeff Moden (8/25/2016)

P.S. You should also avoid the use of any of the "MONEY" datatypes and small scale DECIMAL types for any work with any operators other than simple addition and subtraction.

I've been bitten by that problem too. I couldn't get to match the average until I discovered that the money data type was being used.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

GilaMonster (8/26/2016)
Start by identifying how many digits of precision you need.
Because, even if we had infinite precision calculations, when displaying the data back to users, we're not going to be displaying 28 decimal places (unless you're doing sub-atomic physics maybe), so we will be rounding at some point for display

Absolutely agreed. I typically do the calculations with the maximum scale (NOT implying always using DECIMAL(38,28). "It Depends") I can get and then do the proper rounding for display. If it's important to maintain the precision and scale of the answers in a table, I have no qualms about doing so. For example, the "hidden" balance remaining in mortgage tables just so Granny doesn't beat me to death with her calculator (she DOES mind the pennies). 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

waxingsatirical (8/26/2016)
While I have your attention though... how do you get around the decimal rounding problem:
declare @a decimal (38,28) = 1.123456789
declare @b decimal (38,28) = 1.123456789
declare @c decimal (38,28) = 1.123456789
select @a * @b * @c
?

That's exactly where what Gail said comes into play and what I mean by "using the MAX scale I can get". If you look again at the following URL...

https://msdn.microsoft.com/en-us/library/ms190476.aspx

... you'll see the precision/scale rules for multiplication.

Operation Result precision Result scale*

--------- ---------------- -------------

e1 * e2 p1 + p2 + 1 s1 + s2

Ah... but then there's that bloody asterisk, which means we have a note to read. That note states that...

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Why they decided to do such a thing instead of displaying the answer with the max scale available, I'll never know. According to the other "Result precision" and "Result scale" formulas on that page, the scale of "6" frequently appears and, although not stated in the note above, appears to be the scale that the results are usually reduced to. It's a real pain in the butt because even a 4 function calculator is better than that.

To get around it, you have to do like Gail said. If you want to maintain the "same math" that any cheap calculator would do, you have to make sure that you don't violate a precision of 38 (FLOAT is variable and its scale is only 15 max when there's nothing to the left of the decimal point). In Luis' terms, that means that you can't be lazy about picking your datatypes. The following, which is re-datatyped from your example, gives the answer (well, up to about 13 digits of scale anyway) that a 4 function calculator bought at a dollar store would correctly give without so much worry.

declare @a decimal (12,9) = 1.123456789

declare @b decimal (12,9) = 1.123456789

declare @c decimal (12,9) = 1.123456789

select @a * @b * @c

Result:

1.417976779622360717860897069

So, you are absolutely correct in saying that there are "rounding" problems (truncation, really) but FLOAT won't fix those. The only way to fix the problem is to do the "hard" thing and set the precision and scale of your datatypes correctly or pay the price in truncated results, which can lead to incorrect answers especially if used in "cascading" formulas.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Joe Celko SSCertifiable Points: 5749 More actions · Answer 6

It would be much better to convince the "developers" that FLOAT is the wrong thing to use unless the scale of the number varies in an astronomical way.

I found one way to convince developers is to go to their manager and show him the GAAP or EU regulations concerning rounding and the number of decimal places. In the US. It used to be for places (I do not know what it is today) and in the EU they had triangulation rules for currency conversion and carry things out the five places. Again, I do not know what the current rules are.

But the argument is now very simple! ROI now means "risk of incarceration" and the boss understands that. :w00t: If he does not, the accounting department will explain it to him, or worse yet, the legal department will explain.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

CELKO (8/26/2016)
It would be much better to convince the "developers" that FLOAT is the wrong thing to use unless the scale of the number varies in an astronomical way.
I found one way to convince developers is to go to their manager and show him the GAAP or EU regulations concerning rounding and the number of decimal places. In the US. It used to be for places (I do not know what it is today) and in the EU they had triangulation rules for currency conversion and carry things out the five places. Again, I do not know what the current rules are.
But the argument is now very simple! ROI now means "risk of incarceration" and the boss understands that. :w00t: If he does not, the accounting department will explain it to him, or worse yet, the legal department will explain.

Heh... incarceration would be an almost welcome vacation from the madness. 😛 I'm more afraid of Granny and her 20 pound purse and umbrella. 🙂

It would be wonderful if we could get MS to realize that there's no need for truncated answers and to use the full precision available to things like the DECIMAL datatype at all times for all calculations and let the user decide what the display scale/rounding should be. Even the calculator in Windows does that. Certainly, most spreadsheets are capable of the same thing. Why not SQL Server and T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27851 More actions · Answer 8

Each of the following could serve your purpose. As others in this post have illustrated, you need to define the precision that will work for you and apply it to the decimal data type. I suggest reading more about decimal at books online.

select cast('0.00004' as decimal(18,05))

select cast('0.00004' as decimal(18,10))

----------------------------------------------------