?
Solved

Rounding digit to specific range

Posted on 2013-11-22
5
Medium Priority
?
198 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

777 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