Solved

Rounding digit to specific range

Posted on 2013-11-22
5
181 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
  • 2
  • 2
5 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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 31

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 31

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.
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.
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.
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…

708 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

17 Experts available now in Live!

Get 1:1 Help Now