Solved

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

Posted on 2015-01-23
89 Views
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!
0
Question by:rascal

LVL 12

Expert Comment

ID: 40567059
where cast(monyfield as integer) = monyfield
0

LVL 69

Accepted Solution

ScottPletcher earned 500 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.
0

LVL 35

Expert Comment

ID: 40567087
Hi,

Try this:
``````use ExpertsExchange
go

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

HTH
David

PS Scott: why not round to 0 dp?
0

LVL 47

Expert Comment

ID: 40567129
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

LVL 35

Expert Comment

ID: 40567173
Dale,
I guess that I saw cents and missed the explanation of the tens of cents ...
0

LVL 1

Author Closing Comment

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

## Featured Post

### Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

#### Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!