SQL: How to query money field for only values with non-zero in the 'cents' portion?

My donation table looks like this:

id      int
donation  smallmoney
... other fields ...

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!
LVL 1
rascalAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
I think this will do it, but haven't fully tested it yet:

WHERE donation <> ROUND(donation, 1)

Edit: Yeah, seems to work.
0
 
FarWestCommented:
where cast(monyfield as integer) = monyfield
0
 
David ToddSenior DBACommented:
Hi,

Try this:
use ExpertsExchange
go

select *
from dbo.SomeTable st
where
	floor( st.SomeColumn ) != ceiling( st.SomeColumn )
;

Open in new window


HTH
  David

PS Scott: why not round to 0 dp?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dale FyeCommented:
David,

Because the OP indicated:

"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."
0
 
David ToddSenior DBACommented:
Dale,
I guess that I saw cents and missed the explanation of the tens of cents ...
0
 
rascalAuthor Commented:
Thanks Scott - yours was the working solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.