gboyer (8/31/2009)
All,I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer
You could use the modulo operator, you first need to multiply your float by whichever number of decimals you want to check, cast as integer, and check the last digit with modulo 10.
Here's how:
DECLARE @f FLOAT
SET @f = 25.300
SELECT count(@f)
WHERE cast((@f * 1000 /* that is for 2 decimals*/) AS INT)%10 = 0
SET @f = 25.301
SELECT count(@f)
WHERE cast((@f * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0
Tell me if that helps,
Cheers,
J-F