3

My donation table looks like this:

id      int
donation  smallmoney
... other columns ...

I wish to run a report selecting ONLY rows where the donation amount has a non-zero in the 'cents' portion of the field. For example, if a row had a donation of 12.66, then I would want to select that row. But if the row had a donation of 12.60, then I would skip that row.

Is such a query even possible? I've been googling with no luck.

Thanks experts!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sage
  • 49
  • 1
  • 2
  • Related, but not duplicate (unless you're using PostgreSQL, in which case, it's a dupe): http://stackoverflow.com/questions/15240652/fetch-records-that-are-non-zero-after-the-decimal-point-in-postgresql – LittleBobbyTables - Au Revoir Jan 23 '15 at 19:13

2 Answers2

5

You could also use the modulus operator.

select * from table 
where (donation * 100.00) % 100 != 0
Bill Gregg
  • 7,067
  • 2
  • 22
  • 39
2

Here is a fairly simply way:

select t.*
from table t
where donation <> cast(donation as int)

This casts the donation to an integer, which removes the cents. If the values are equal, then there were no cents on the original donation. If they are unequal, then there is something in the cents field.

EDIT:

The question has changed since I answered it. The above is the simplest for all the cents being zero. It is easily modified for the final digit:

select t.*
from table t
where donation * 10  <> cast(donation * 10 as int)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786