Solved

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

Posted on 2015-01-23
6
89 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
6 Comments
 
LVL 12

Expert Comment

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

Accepted Solution

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

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now