Search code examples
sqlsql-serverroundingnumber-formatting

Conditional float formatting for amount in SQL Server

I am struggling to format a float number to string with conditional formatting.

Input : Float Number 00000.NN
Output : string 00000.NN

Conditions :

if a variable decimalplace = 2 the convert the number as is to string upto 2 decimal place, no rounding

if a variable decimalplace = 0 the convert the number as is to string upto 2 decimal place. Just trim the decimal part with .00

Example :

if decimal place = 2
    then 123456 will 123456.00
    then 123456.00 will 123456.00
    then 123456.12 will 123456.12

if decimal place = 0
    then 123456 will 123456.00
    then 123456.00 will 123456.00
    then 123456.12 will 123456.00
Solution

  • A combination of ROUND() and FORMAT() is a possible solution:

    DECLARE @place int = 2;

SELECT 
   [Number],
   FORMAT(ROUND([Number], @place), '#.00') AS [FormattedNumber] 
FROM (VALUES
   (123456),
   (123456.00),
   (123456.12),
   (123456.1245)
) v ([Number])