Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Rounding digit to specific range

Posted on 2013-11-22
5
Medium Priority
?
200 Views
Last Modified: 2013-11-23
Needed help for SQL select statement!

For an example I want to round
95.1556 to 95.125
or
5.625 to 5.625

in my Excel query I use Floor(95.1556,0.125) and I got 95.125 but don't know how to do it in SQL statement in SQL 2008 database
0
Comment
Question by:eli411
[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
  • 2
  • 2
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39669941
Since you're talking eighths, an easy way would be to multiply the number by eight, then round, then divide by eight.
Declare @n numeric(19,4) = 95.1556
SELECT ROUND(@n * 8,0) / 8

SET @n = 5.625
SELECT ROUND(@n * 8,0) / 8

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39669981
And what would you want for 95.1897? 95.125 or 95.250?
0
 
LVL 2

Author Comment

by:eli411
ID: 39670249
awking00, thanks for the syntaxes...

Here is my select statement

select MortgageRatePricing
from dbo.MortgageRate

and the output shown as follow

95.1556
96.3024

and I wanted to round them to 0.125 which will be

95.125
96.250

can you show that in the syntax using your example?  Thank you!
0
 
LVL 2

Author Comment

by:eli411
ID: 39670267
never mind!  Got it to work!  Here is the final syntax just for someone needed in the future.

select round(cast(MortgageRatePricing as numeric(19,4))*8,0)/8 as MortgageRatePricing
from dbo.MortgageRate
0
 
LVL 32

Expert Comment

by:awking00
ID: 39671475
The reason I asked my question was that I wasn't sure if you wanted the decimal places "truncated" to the eighths or if it should be rounded up to the next eighth if called for. The examples you've provided work for both since they "truncate" to the previous eighth and round down to the previous eighth. However, 95.1897 would "truncate" to 95.125, but would round to 95.250 (95.1897 times 8 = 761.5176 which rounds to 762 and divided by 8 = 95.250).
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

604 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