Solved

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

Posted on 2015-01-23
6
110 Views
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!
0
Comment
Question by:rascal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40567059
where cast(monyfield as integer) = monyfield
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher 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

by:David Todd
ID: 40567087
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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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

by:David Todd
ID: 40567173
Dale,
I guess that I saw cents and missed the explanation of the tens of cents ...
0
 
LVL 1

Author Closing Comment

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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 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