Thursday, January 5, 2017

SQL Server - Rounding Off Decimal to 0.5

This is something I had to use with one of my projects today. As a part of the implementation, an average value had to be rounded off to 0.5 and result should be something like 3.0, 3.5, 4.0, etc. For example, if the value is 3.6, it has to be saved as 3.5, if the value is 3.8, it has to be saved as 4.0.

Since it is not something we always do, thought to share the code. Here is the code I wrote using few functions;

DECLARE @Number decimal(2,1) = 3.2

-- This will return the value as 3.0
SELECT CASE WHEN @Number % 1 IN (.3, .4, .6, .7) THEN Convert(decimal(2,1), Floor(@Number) ) + .5
   WHEN @Number % 1 IN (.8, .9) THEN Convert(decimal(2,1), Ceiling(@Number))
   WHEN @Number % 1 IN (.1, .2) THEN Convert(decimal(2,1), Floor(@Number))
   ELSE @Number END;

at

2 comments:

  1. dhlotterJanuary 31, 2017 at 3:45 AM

    Hi Dinesh,

    You should be able to do some magic with the below (substituting 3.8 for your number).

    SELECT ROUND((3.8) * 2, 0) / 2

    It might not work exactly for what you require, but will work in most cases.

    ReplyDelete
  2. Dinesh PriyankaraJanuary 31, 2017 at 9:05 AM

    Excellent! Thank you very much for sharing it Hermann, it looks like it works with my requirement.

    ReplyDelete