Select Query

Hello,

Is it possible to select from a float type column only the data that has > 2 digits after the decimal point?

i.e. it would return 1.2222222222, 1234.522, 4334334.5454545
but not 1.0, 1234.53

Thanks,

Jimbo

where data > truncate(data, 2)
see the manual

Thank you for the reply pabloj.

I am using MS SQL 2000 which throws an incorrect syntax error for your suggestion.

Any other ideas?

lol @ pablo suggesting RTFWM (read the wrong manual)

Doh ....

For the OP, check BOL (Books On Line) the MsSQL manual

The only truncate I can find for MSSQL 2000 is truncate table.
I've also tried to cast and convert the floats to varchars which I could then perform string operations on but the precision gets lost.

select convert(varchar(20),data) from table

&

select cast(data as varchar(20)) from table

e.g. both return 259.28 instead of 259.27999999999997


Any help would be appreciated!

Regards

For MsSQL convert() to decimal with the appropriate number of decimals.

The problem is I need to convert the column from datatype float to decimal and I need to verify that all the float type data will be rounded appropiately.

To do this I need a means of selecting all the data with <2 places after the decimal point before the type alteration so it can manually be checked against the resulting decimal values. All relevant float entries currently contain 8 places following the decimal point e.g. 999.99999997

The following throws an arithmetic overflow error
convert(decimal(8,8),data)???

decimal(8,8) means you can store only numbers less than 1, since all the relevant digits are on the right of the decimal point

perhaps you meant DECIMAL(16,8) ?

Thanks r937 & pabloj!!!

I actually used DECIMAL(18,14) as I just realised the floats have 14 places post the decimal point.

Regards,

Jim