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

Posted on 2015-01-23
Medium Priority
Last Modified: 2015-01-24
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!
Question by:rascal
LVL 12

Expert Comment

ID: 40567059
where cast(monyfield as integer) = monyfield
LVL 70

Accepted Solution

Scott Pletcher earned 2000 total points
ID: 40567065
I think this will do it, but haven't fully tested it yet:

WHERE donation <> ROUND(donation, 1)

Edit: Yeah, seems to work.
LVL 35

Expert Comment

by:David Todd
ID: 40567087

Try this:
use ExpertsExchange

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

Open in new window


PS Scott: why not round to 0 dp?
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LVL 50

Expert Comment

by:Dale Fye
ID: 40567129

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."
LVL 35

Expert Comment

by:David Todd
ID: 40567173
I guess that I saw cents and missed the explanation of the tens of cents ...

Author Closing Comment

ID: 40568344
Thanks Scott - yours was the working solution.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question