CODEWITHSUNDEEP
sql-server-2008
Anurag
Anurag

Reputation: 17

how to round of decimal digit to nearest 50

How can it convert or round off the decimal digit to nearest 50. for e.g if i get 2.00 to 2.49 then it may change to 2.50 , 2.50 to 2.99 then it may change to 3.00. pls solve if anyone knows.

Upvotes: 0

Views: 379

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Something like this should produce your required rounding up:

SELECT CEILING(<input> * 2.0) / 2.0

Where <input> is the column or expression that's currently producing the values you want to round.

Upvotes: 2

Deep
Deep

Reputation: 3202

I don't know if this is an efficient way or not but I tried this :

IF OBJECT_ID('MyTable','U') IS NOT NULL 
    DROP TABLE MyTable
GO
CREATE TABLE MyTable (num DECIMAL(10,2))
GO
INSERT INTO MyTable 
values (1.00),(1.01),(1.49),(1.50),(1.51),(1.99),(2.00),
(11.00),(11.01),(11.49),(11.50),(11.51),(11.99),(12.00)

SELECT [num],
       CASE
         WHEN ( [num] - [Nbr] ) BETWEEN 0.01 AND 0.49 THEN [Nbr] + 0.5
         ELSE [Nbr]
       END AS [Result]
FROM   (SELECT [num],
               ROUND(num, 0) AS [Nbr]
        FROM   MyTable) t

Note: Case when condition can be modified as per requirement.

Upvotes: 0

Related Questions