4

I am wanting to round down these numbers, and I have tried several different ROUND() functions but can not get it exactly right. Take this sample DDL

Create Table #Fun
(
  value1 float
)

Insert Into #Fun Values
(1002313), (1888222), (1666222), (1222333)

Select
ROUND([value1], -5) As Rounded
FROM #Fun

Drop Table #Fun

In the below the left column is what SQL produces and the right is what I want it to be. What must be altered in SQL in order to provide the output I am requesting?

1000000 = is fine
1900000 = 2000000
1700000 = 1500000
1200000 = 1000000
4

It actually looks like you're trying to get the nearest 500000.

Look here: https://stackoverflow.com/questions/1702080/round-to-nearest-5-in-sql-server

In your case, use ROUND(value1/500000.0,0)*500000

4

While it appears you want to round to the nearest 500000 and @Forrest's answer is correct for that I thought I would throw in an answer for the stated question. If you always want to round down then use FLOOR instead of ROUND. Unfortunately FLOOR doesn't have a second parameter so you have to use the division trick Forrest mentioned.

CREATE TABLE #Fun
(
  value1 float
)

INSERT INTO #Fun VALUES
    (1002313), (1888222), (1666222), (1222333)

SELECT ROUND([value1], -5) As Rounded, FLOOR([value1]/100000)*100000 AS Floored,
    FLOOR([value1]/500000)*500000 AS Floored500k
FROM #Fun

DROP TABLE #Fun

Output

Rounded Floored Floored500k 
1000000 1000000 1000000
1900000 1800000 1500000
1700000 1600000 1500000
1200000 1200000 1000000

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.