Getting records which only has decimal values

niroshan

Hi Guys,

I have a bit of a strange requirement which i need help from you guys. I have a table called 'quote_prices' which has a column named 'price' which is a double.

In this table I have quite a lots of records which some has decimal values as well. how can i extract the records which only has decimal values???

Why do i need it?

This table contains several years of quote prices for companies and recently the vendor has change the price currency so as a result i have all my old prices in decimals and where as new values in thousands (x 1000).

Ex:
old price : 0.25
new price : 250 (0.25 * 1000)

this shows huge price difference and as a solution i thought of multiply all the decimal values with 1000 to get rid of all the decimal parts.

Thanks,
best regards,
Niroshan

Roger_Ramjet

If you are talking about current/prices then you should NEVER store them as floating point numbers. Floats are approximate and Money is always exact.

As the manual sez

" The DECIMAL data type is a fixed-point type and calculations are exact. In MySQL, the DECIMAL type has several synonyms: NUMERIC, DEC, FIXED. The integer types also are exact-value types.

The FLOAT and DOUBLE data types are floating-point types and calculations are approximate. In MySQL, types that are synonymous with FLOAT or DOUBLE are DOUBLE PRECISION and REAL. "

So rather than converting the decimals you should be converting the doubles. To do that you just need to run CAST

Now I don't know how you are storing the different types in the same column to start with ??? Should not be possible if you have specified the data type correctly. Most likely you are getting a silent type conversion anyway.