identify data values with 3 numbers after decimal point instead of the desired 2

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
help identifying values in a table.

i have a table with a column called transaction amt.     this column is (money, int) datatype

unfortunately, there are some money transactions have partial pennies showing up.   ie... transaction amount might be   59.787.    we only need 2 decimal places

does anyone know how to find all transactions where there are 3 digits after the decimal point instead of the desired 2.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT/UPDATE
WHERE [transaction amt] % 0.01 > 0.000

Author

Commented:
have no idea why that worked... but it did
Thanks Scott!
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
Why it works:

% is remainder after division (modulus).  The modulus result must be smaller than the divisor, in this case 0.01.  So it shows you any values below .01 that are not 0, i.e., thousandths.

For example:
31 % 4 = 3, because that's the remainder after dividing 31 by 4.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial