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
if you convert a float to a string, there is always going to be more than three chars after the decimal point.
check out the results of my example table, you get values like 151.000000000000000000000 for the conversion when it rounds nice an pretty....so it wouldn't help to convert to a string.
i think the thing to do is to forget casting to string,a nd jsut find items that are not equal to the round to two decimal palces of the number....that would tell you that 26.92 26.920000076293945
but that 151.00 = 151.000000000000000000000
create table #Example(exampleId int identity(1,1), ExampleTotal float)
insert into #Example
select 150.0 + (1.0 / RW) from (
select top 30 row_number() over (order by id) as RW,id from sysobjects
) x
--results
/*
151.000000000000000000000
150.500000000000000000000
150.333333333333333333333
150.250000000000000000000
150.200000000000000000000
150.166666666666666666666
150.142857142857142857142
150.125000000000000000000
150.111111111111111111111
*/
select * from #Example where round(ExampleTotal,2) ExampleTotal --two decimal places
Lowell