TSQL - make a literal float value
- by David B
I understand the host of issues in comparing floats, and lament their use in this case - but I'm not the table author and have only a small hurdle to climb...
Someone has decided to use floats as you'd expect GUIDs to be used. I need to retrieve all the records with a specific float value.
sp_help MyTable
-- Column_name Type Computed Length Prec
-- RandomGrouping float no 8 53
Here's my naive attempt:
--yields no results
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping = 0.867153569942739
And here's an approximately working attempt:
--yields 2 records
SELECT RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.867153569942739 - 0.00000001
AND 0.867153569942739 + 0.00000001
-- 0.867153569942739
-- 0.867153569942739
In my naive attempt, is that literal a floating point literal? Or is it really a decimal literal that gets converted later?
If my literal is not a floating point literal, what is the syntax for making a floating point literal?
EDIT: Another possibility has occurred to me... it may be that a more precise number than is displayed is stored in this column. It may be impossible to create a literal that represents this number. I will accept answers that demonstrate that this is the case.
EDIT: response to DVK.
TSQL is MSSQLServer's dialect of SQL.
This script works, and so equality can be performed deterministically between float types:
DECLARE @X float
SELECT top 1 @X = RandomGrouping
FROM MyTable
WHERE RandomGrouping BETWEEN 0.839110948199148 - 0.000000000001
AND 0.839110948199148 + 0.000000000001
--yields two records
SELECT *
FROM MyTable
WHERE RandomGrouping = @X
I said "approximately" because that method tests for a range. With that method I could get values that are not equal to my intended value.
The linked article doesn't apply because I'm not (intentionally) trying to straddle the world boundaries between decimal and float. I'm trying to work with only floats. This isn't about the non-convertibility of decimals to floats.
