Trouble querying float column

[jakem]

I am trying to query a table that contains a Primary Key which is set as a float data type and a nvarchar field that stores names. For instance I have a record where the ID is 75.01 and the name is Bob Tucker.

When I try to search for this record using the followwng query no records are returned:

Select * From Author Where ID = 75.01

However, if I use LIKE it works fine. For example:

Select * From Author Where ID Like 75.01

I presume this is because the actual number stored in the database has a whole lot of numbers after the decimal point but I would appreciate any advice on a way of forcing the query to round to two decimal places or something as I am not sure using LIKE is going to be accurate enough.

I would also appreciate it if responses could also be emailed to jake@librarysolutions.com.au so I can be sure to get them.

Thanks in advance,

Jake.

 

[fluteplr]

A float column is a terrible choice for a primary key, if you really need the decimals, change it to a nmueric whith a fixed number of decimal places.

 

[Guest_imported]

try this:


Select * From Author Where cast(ID as decimal(5,2)) = 75.01

 

[fluteplr]

You can do that, but since sql won't use the index the response time will be slow as soon as you get a resonable number of records.