How do I force the value of a float data type field in SQL Server to be precisely what I need it to be?

I have two tables where I need to compare the values of a float field for an exact match.  Ultimately, I will solve my issue in code.  However, I have a one-time need to force the two to be equal.

In the first table, the value is stored as 1.33.  In the second table, the value is stored as 1.33000004291534

I need to force the second table to be 1.33.

How can I do that?


Thanks ...
LVL 4
g_johnsonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
You may find that to be impossible since floats are binary values and not exact decimal values.  The most common recommendation for comparing float values is to compare against a range from greater than to less than.  Or "x+-0.001" to define the range.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
You could convert the value to a decimal which does have precision and scale.

SELECT CAST(myValue AS DECIMAL(9,2))
Scott PletcherSenior DBACommented:
You could use ROUND.  The minor decimal variations in a float value will never be enough to cause ROUND to push the value up:

WHERE ROUND(float_column, 2) = decimal_column
or
WHERE ROUND(float_column, 2) = ROUND(other_float_column, 2)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
g_johnson, do you still need help with this question?
g_johnsonAuthor Commented:
Thank you for the explanation of why I couldn't force the value to be what I needed it to be.
Dave BaldwinFixer of ProblemsCommented:
You're welcome, glad to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.