How to round to the nearest .5
- MSSQL has the round function for rounding, and you can decide to which digit to round to, see: How to round up and round down with MSSQL
- There are occasions when you need to round not to the nearest digit but to the nearest half, and round does not have such an option.
- The way to round to the nearest .5 is to multiply by 2, round and then finally divide by 2.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
Data used
- USE adventureworks;
- go
- SELECT SalesOrderID, SalesOrderDetailID, OrderQty,
- ProductID, UnitPrice, LineTotal
- FROM sales.salesorderdetail
- WHERE salesorderid between 43659 and 43661;
- go
SalesOrderID SalesOrderDetailID OrderQty ProductID UnitPrice LineTotal ------------ ------------------ -------- ----------- --------------------- --------------------------------------- 43659 1 1 776 2024.994 2024.994000 43659 2 3 777 2024.994 6074.982000 43659 3 1 778 2024.994 2024.994000 43659 4 1 771 2039.994 2039.994000 43659 5 1 772 2039.994 2039.994000 43659 6 2 773 2039.994 4079.988000 43659 7 1 774 2039.994 2039.994000 43659 8 3 714 28.8404 86.521200 43659 9 1 716 28.8404 28.840400 43659 10 6 709 5.70 34.200000 43659 11 2 712 5.1865 10.373000 43659 12 4 711 20.1865 80.746000 43660 13 1 762 419.4589 419.458900 43660 14 1 758 874.794 874.794000 43661 15 1 745 809.76 809.760000 43661 16 1 743 714.7043 714.704300 43661 17 2 747 714.7043 1429.408600 43661 18 4 712 5.1865 20.746000 43661 19 4 715 28.8404 115.361600 43661 20 2 742 722.5949 1445.189800 43661 21 3 775 2024.994 6074.982000 43661 22 2 778 2024.994 4049.988000 43661 23 2 711 20.1865 40.373000 43661 24 2 741 818.70 1637.400000 43661 25 4 776 2024.994 8099.976000 43661 26 2 773 2039.994 4079.988000 43661 27 2 716 28.8404 57.680800 43661 28 2 777 2024.994 4049.988000 43661 29 5 708 20.1865 100.932500 (29 row(s) affected)
Rounding to the nearest .5
- SELECT SalesOrderID, SalesOrderDetailID,
- OrderQty, ProductID, UnitPrice, LineTotal,
- ROUND(2 * LineTotal,0) / 2 AS 'Rounded amt'
- FROM sales.salesorderdetail
- WHERE salesorderid between 43659 and 43661;
- go
SalesOrderID SalesOrderDetailID OrderQty ProductID UnitPrice LineTotal Rounded amt ------------ ------------------ -------- ----------- --------------------- --------------------------------------- --------------------------------------- 43659 1 1 776 2024.994 2024.994000 2025.000000 43659 2 3 777 2024.994 6074.982000 6075.000000 43659 3 1 778 2024.994 2024.994000 2025.000000 43659 4 1 771 2039.994 2039.994000 2040.000000 43659 5 1 772 2039.994 2039.994000 2040.000000 43659 6 2 773 2039.994 4079.988000 4080.000000 43659 7 1 774 2039.994 2039.994000 2040.000000 43659 8 3 714 28.8404 86.521200 86.500000 43659 9 1 716 28.8404 28.840400 29.000000 43659 10 6 709 5.70 34.200000 34.000000 43659 11 2 712 5.1865 10.373000 10.500000 43659 12 4 711 20.1865 80.746000 80.500000 43660 13 1 762 419.4589 419.458900 419.500000 43660 14 1 758 874.794 874.794000 875.000000 43661 15 1 745 809.76 809.760000 810.000000 43661 16 1 743 714.7043 714.704300 714.500000 43661 17 2 747 714.7043 1429.408600 1429.500000 43661 18 4 712 5.1865 20.746000 20.500000 43661 19 4 715 28.8404 115.361600 115.500000 43661 20 2 742 722.5949 1445.189800 1445.000000 43661 21 3 775 2024.994 6074.982000 6075.000000 43661 22 2 778 2024.994 4049.988000 4050.000000 43661 23 2 711 20.1865 40.373000 40.500000 43661 24 2 741 818.70 1637.400000 1637.500000 43661 25 4 776 2024.994 8099.976000 8100.000000 43661 26 2 773 2039.994 4079.988000 4080.000000 43661 27 2 716 28.8404 57.680800 57.500000 43661 28 2 777 2024.994 4049.988000 4050.000000 43661 29 5 708 20.1865 100.932500 101.000000 (29 row(s) affected)
- SalesOrderID: 43659, SalesOrderDetailID: 1 did properly round up
- SalesOrderID: 43659, SalesOrderDetailID: 8 did properly round down
- SalesOrderID: 43661, SalesOrderDetailID: 27 did properly round down
- SalesOrderID: 43661, SalesOrderDetailID: 29 did properly round up
- Line 3: round(2 * LineTotal,0) / 2: round(LineTotal,0) removes all the digits after the period.
- Line 3: by first multiplying by 2, then again dividing by 2 you will get the nearest .5