Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Rounding digit to specific range

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
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.
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…
Suggested Courses

571 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