Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2015-01-23
6
Medium Priority
?
123 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 70

Accepted Solution

by:
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.
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 48

Expert Comment

by:Dale Fye
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.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

721 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